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

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH
GO

change to FOR ATTACH -- > FOR ATTACH_FORCE_REBUILD_LOG

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH_FORCE_REBUILD_LOG
GO

Answer 2

If you run sql server 2012 you can get this error by trying to attach an older version of an mdf-file. ex an mdf file from sql server 2008.

Answer 3

I've had the same issue when re-attaching database after detaching it and moving ldf and mdf files from drive C to F.

In order to fix it I had to add OWNER RIGHTS principal to both files and gave it full control over them in the Security tab of the Properties dialog.

Answer 4

it can be fixed easly but radicaly, just go to the folder where you have stored mdf file. select file-> Right click ->click on properties and give full permissions to file for logged in user Security.

Answer 5

I attached the mdf file by right clicking the database and removing the log file AdventureWorks2012_Data_log.ldf in the wizard . The mdf file was placed in the following location

    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

The above method helped me to resolve the issue .

Answer 6

It is in fact NTFS permissions, and a strange bug in SQL Server. I'm not sure the above bug report is accurate, or may refer to an additional bug.

To resolve this on Windows 7, I ran SQL Server Management Studio normally (not as Administrator). I then attempted to Attach the MDF file. In the process, I used the UI rather than pasting in the path. I noticed that the path was cut off from me. This is because the MS SQL Server (SQLServerMSSQLUser$machinename$SQLEXPRESS) user that the software adds for you does not have permissions to access the folder (in this case a folder deep in my own user folders).

Pasting the path and proceeding results in the above error. So - I gave the MS SQL Server user permissions to read starting from the first directory it was denied from (my user folder). I then immediately cancelled the propagation operation because it can take an eternity, and again applied read permissions to the next subfolder necessary, and let that propagate fully.

Finally, I gave the MS SQL Server user Modify permissions to the .mdf and .ldf files for the db.

I can now Attach to the database files.