Access is denied when attaching a database

问题:

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?

回答1:

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

回答2:

Thank you for all of the comments. Some of you helped to lead me to the answer. Here's what I found:

It was an NTFS permission problem, and not a SQL problem. Further, it looks kind of bug-like (and it's repeatable).

The problem: The account that I was using had full control NTFS permissions to the mdf and ldf files. However, it had those permissions through group membership (the Local Administrators group had permissions, and my account is a member of local admins). (I verified the permissions)

If I try to do the attach, connect to SQL Server as me (where I am in the admins group), it fails with the NTFS problem.

However, if I grant the same file permissions that the local admin group has directly to my Domain Account, then I can attach with no problems.

(oh, and yes, I checked the local groups on this machine, and I verified that my domain account is indeed a member of the local admins group).

So, it looks as though the error occurs because some code (either in SQL Server or Management Studio) checks for the permissions that the user account holds, but it doesn't go so far as to check group permissions that the user account inherits.

That sounds weird to me, but I can reproduce it over and over again, so I have concluded that it is the answer.

Update: I reported this as a bug: https://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-inherited

回答3:

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

回答4:

Add permission to the folder where your .mdf file is.

Check this name: NT Service\MSSQLSERVER

And change the Location to your server name.

回答5:

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".)

回答6:

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

回答7:

A SQL2005 database can be attached in this way in Windows 7:

start menu >
 all program >
  Microsoft sql server 2005 >
   sql server management studio >
    right click >
     run as administrator >
      click ok

And then attached database successfully completed.

回答8:

When you login as sa (or any Sql Server account), you're functioning as the SQL Server service account, when you're logged in as you, you have the permissions of your account. For some reason you don't have the appropriate file access but the service account does.

回答9:

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

回答10:

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.

回答11:

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.

回答12:

I found this solution: Right click on folder where you store your .mdf file --> click Properties --> choose Security tab, click Edit... and give it full control. Hope this helps!

回答13:

Every time I have run into this issue was when attempting to attach a database that is in a different directory from the default database directory that is setup in SQL server.

I would highly recommend that instead of jacking with permissions on various directories and accounts that you simply move your data file into the directory that sql server expects to find it.

回答14:

I just wanted to add this information as well.

http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/

Solution

You get this error because two different logins did the detach and attach operations. So the files, when detached, were owned by the first login, but the attach failed because the login that was used was not the owner of the mdf and ldf files.

When we detach database files, the owner becomes the person who did the detach command, so to resolve the issue we need to change or add the other login as the owner of the mdf and ldf files.

Right click on the "filename.mdf" file and select properties to check the permissions of the mdf file. Here we can see that only one account has permission to the "filename.mdf" file because that was the account that was used to detach the database.

To resolve this issue, click on the Add... button to add the other login or any other login needed and give the login Full Control. You should do this for the "ldf" file as well. Once you have completed this task click the OK button. (Note for other OS versions you may have an Edit option , click this first and then you will see the Add... option.)

回答15:

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.

回答16:

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?

回答17:

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.

回答18:

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 .

回答19:

I was reading this page and they have an interesting sentence in there:

Caution: Be very selective when adding users to these roles. For example, sysadmin maps out to dbo in every database and is the equivalent of logging in using the sa account.

Of course, they also have this:

Permissions that are granted to users and roles and are database specific. All permissions are cumulative with the exception of a DENY. A denied permission at either a user level or at a role level overrides the same permission granted via other role memberships with the exception of the sysadmin fixed server role. (A sysadmin retains all permissions, even if a role they are a member of has a DENY permission.)

So if you're a domain admin and in SQL 'sysadmin' group, the world should be your crustacean.

Of course, according to Microsoft, you should be taking a quick look at these two pages:
Link to Database Prerequisites

Link to Installing Databases

You're being naughty and trying to attach them manually :) Seriously though, do you have all the prerequisites for the AdventureWorks2008 database?
I suspect this is just another Microsoft oddity/edge case, but I could be wrong.

回答20:

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?

回答21:

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

回答22:

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.

回答23:

I got this error as sa. In my case, security didn't matter. I added everyone full control to the mdf and ldf files, and attach went fine.

回答24:

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.

回答25:

I have solved the problem by just move the .mdf file that you want to attach to the public folder, in my case I moved it to the users/public folder. Then I attach it from there without any problem. Hope this helps.

回答26:

For those who could not fix the problem with the other solutions here, the following fix worked for me:

Go to your "DATA" folder in your SQL Server installation, right click, properties, security tab, and add full control permissions for the "NETWORK SERVICE" user.

http://decoding.wordpress.com/2008/08/25/sql-server-2005-expess-how-to-fix-error-3417/

(The above link is for SQL 2005, but this fixed a SQL 2008 R2 installation for me).

Some additional info: This problem showed up for me after replacing a secondary hard drive (which the SQL installation was on). I copied all the files, and restored the original drive letter to the new hard disk. However, the security permissions were not copied over. I think next time I will use a better method of copying data.

回答27:

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

回答28:

Copy Database to an other folder and attach or Log in SQLServer with "Windows Authentication"

回答29:

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.

回答30:

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;