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

This sounds like NTFS permissions. It usually means your SQL Server service account has read only access to the file (note that SQL Server uses the same service account to access database files regardless of how you log in). Are you sure you didn't change the folder permissions in between logging in as yourself and logging in as sa? If you detach and try again, does it still have the same problem?

Answer 2

For what it's worth to anyone having the particular variation of this problem that I had:

• SQL Express 2008
• Visual Studio 2010 Premium

Through the context menu of the App_data folder I had created a SQL Express database for debugging purposes. The connection string (used by NHibernate) was as follows:

Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|DebugDatabase.mdf;
Database=DebugDatabase;
Trusted_Connection=Yes;

This gave me the same "Access denied" error on the database file. I tried giving various users Full Control to the folder and files, at one point even to "Everyone". Nothing helped, so I removed the added permissions again.

What finally solved it was to open the Server Explorer in Visual Studio, then connect to the MDF, and detach it again. After I'd done that my web app could access the database just fine.

PS. Credits go to this blog post I found while googling this particular problem, triggering the idea to attach/detach the database to solve the issue.

Answer 3

I moved a database mdf from the default Data folder to my asp.net app_data folder and ran into this problem trying to set the database back online.

I compared the security settings of the other file databases in the original location to the moved files and noticed that MSSQL$SQLEXPRESS was not assigned permissions to the files in their new location. I added Full control for "NT SERVICE\MSSQL$SQLEXPRESS" (must include that NT SERVICE) and it attached just fine.

It appears that the original Data folder has these permissions and the files inherit it. Move the files and the inheritance breaks of course.

I checked another project's mdf file which I created directly into its app_data folder. it does not have MSSQL$SQLEXPRESS permissions. Hmmm. I wonder why SQL Express likes one but not the other?

Answer 4

This problem is caused by UAC (User Account Control), isn't it? Although your user account is a member of Administrators group, the UAC in Windows 7 doesn't allow you do do administrator things unless you run programs "as administrator". It is not a real bug in SQL Server or Management Studio or whatever. (Although it could possibly know the problem and ask you for elevated permissions instead of just complaining "error 5".)

Answer 5

With me - Running on window 8 - RIght click SQL Server Manager Studio -> Run with admin. -> attach no problems

Answer 6

I had the same issue when attaching a database. It wasn't a SQL issue it was an account issue. Go to the panel control/User Account Control Settings/Set to "never notify". Finally,restart the computer and it worked for me.