Access is denied when attaching a database

Question

I am using SQL Server 2008 developer edition. I was trying to attach the AdventureWorks2008 database.

When I tried to attach, I received an "access is denied" error. According to the event log, it came from the O/S:

Open failed: Could not open file D:\ProjectData\AdventureWorks\AdventureWorksLT2008_Data.mdf for file number 0. OS error: 5(Access is denied.).

I thought "NTFS problem", but System (and I) have modify access to both files.

I found that I can successfully attach the database if I log in as sa, but my user account won't work.

I am a member of the local administrators group on my machine, and I am in the sysadmins role in SQL Server instance.

Any idea why I had to be logged in as sa?

Answer 1

The sa user uses NTFS accounts SQLServerMSSQLUser$<computer_name>$<instance_name> and SQLServerSQLAgentUser$<computer_name>$<instance_name> to access the database files. You may want to try adding permissions for one or both these users.

I don't know if solves your problem since you say you have no problems with the sa user, but I hope it helps.

Answer 2

I'd like to add additional info to the answers that were posted.

Be careful when detaching the database because the windows user you are logged in as becomes the only user with permissions to the .mdf file! The original permissions the .mdf file had which included the user SQLServerMSSQLUser$<computer_name>$<instance_name> and the Administrators account get overwritten by whichever windows user you are logged in as (not sql server user). Boom, all permissions gone just like that. So do as others have said and right click your .mdf file and double check the permissions.

I ran into this problem because I used SSMS to connect to the database (doesn't matter which sql server account) and detached the database. After doing that my windows user was the only one that had any permissions to the .mdf file. So later on when I tried to attach the db using the sa account, it threw the "access denied" error.

To keep the original permissions in tact you should take the database offline, then detach, then attach in that order like so:

USE [master]
GO
-- kick all users out of the db
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

-- Take the Database Offline
ALTER DATABASE mydb SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

-- detach the db
EXEC master.dbo.sp_detach_db @dbname = N'mydb'
GO

Answer 3

Run SQL Server Management Studio as an Administrator. (right click-> run as administrator) worked for me with Windows 7 - SQL server 2008 R2

Answer 4

Run SQL Server Management Studio as an Administrator. (right click-> run as administrator) that took care of all the weirdness in my case.

SQL SRV EXPRESS 2008 R2. Windows 7

Answer 5

In my case what solved the problem was the folowing:

USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = 'C:\Program Files\Microsfot SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWors2008R2_Data.mdf')
FOR ATTACH_REBUILD_LOG

Answer 6

I struggled with SSMS (2016) to attach the AdventureWorks2012 database. But had success with this code, taken from a CodeProject article by Mohammad Elsheimy:

CREATE DATABASE AdventureWorks2012
    ON PRIMARY (FILENAME='D:\Dev\SQL Server\AdventureWorks2012.mdf')
    FOR ATTACH;