SQL Server Home
Access Denied attaching a database when permissions are inherited
as By Design
3/5/2010 4:41:53 PM
User(s) can reproduce this bug
If you attempt to attach a database, and the mdf/ldf files are in a directory where your user account has privileges only through a group, you get an access denied error. If you grant the same priivlege set directly to your user account, the attach succeeds.
SQL Server 2008 - Developer Edition
Windows 7 Enterprise
Operating System Language
Steps to Reproduce
(all done on a single Windows 7 machine -- client and server on same box)
For all of these steps, use a Domain Account that has admin access to the Win7 machine. In my case, it was originally through 2 levels of indirection:
The domain account is a Domain Admin
The Domain Admins group is a member of the Administrators group on the Win7 machine.
through this chain, the domain account is an admin on the Win7 machine.
Also, the domain account is a sysadmin on the SQL Server instance.
UAC is enabled.
The SQL Server instance is running as the Local System account.
Create a directory named "ProjectData" on the Win7 machine.
In my case, I had the default set of NTFS permissions:
system and local admins group had full control. Users and authenticated users had the default read/create access, but not modify. (I had created the directory, and had not touched the NTFS permissions).
Note that the domain account should NOT directly appear in the access list.
Copy the Adventureworks 2008LT mdf/ldf files to the directory
Use SQL Management Studio to connect to a SQL Server instance.
Use integrated security.
Attempt to attach the database.
You get an error (in the Event Log, you discover that it is an NTFS permission denied.
Now, if you go to the directory that you created and directly assign to your Domain Account the same permissions that are assigned to the local admin group, the attach will succeed.
(it appears that the tool doesn't "know" that your domain account is a local admin.)
Is this related to the fact that MSSQL is runnign as the _local_ system account, and has no domain permissions?
Expected for the attach to succeed.
to post a comment.
Please enter a comment.
on 2/10/2014 at 12:33 PM
I can confirm this still occurs on windows server 2008 R2 Standard using (localdb)\v11.0. In my case the AD user was part of the local Administrators group but would still attach the mdf database as read-only despite the file having full-control access to Administrators. Giving the AD user explicit permissions to the file corrected the problem. The comment from Bryant E. Byrd below is spot on.
Please consider reopening this bug.
on 4/20/2013 at 11:06 PM
I was happen to me the same problem. Just makes this steps:
1.Delete LDF file from folder where is saved your both files.
2. Go to the Management studio and try to attach the same database.
3.When you come in window in which you select database to attach, you will see in second list its remarked that "LDF file is not founded" ,
4.You just select LDF file and kick on button Remove.
5. Click button OK and your database will be attached.
This happened because something of your privileges are saved in LDF file. For that reason you must to clear this information.
on 1/2/2013 at 11:27 AM
I ran into the same problem.
I created a database with my domain user with local admin and sysadmin rights. I detached it and copied it to a network drive. When I had a Vista OS, I used to copy the detached database from the network drive back to a local directory on my box and attach it with no problems. My computer was upgraded from Vista to Windows 7 and now the exact same process gives me an Access Denied. I have to give myself full access rights to the directory that I just created before I can attach the database.
I created the directory. Why do i need to give myself rights to a directory that I created before I can attach a database in that directory? It makes no sense to me.
on 10/7/2012 at 12:11 PM
New to SQL server. Just installed SQL Server 2008 R2 on Windows 7 pro. Tried to attach AdventureWorksLT. Run into this error. I am the in the local administrator group. I have to manually change folder permission to give my self full control to AdventureWorks database folder, this doesn't make sense since I am the local admin. Can't believe MSFT can't reproduce this error.
Bryant E. Byrd
on 7/19/2012 at 4:57 PM
This issue goes counter to Windows security concepts. Checking to see if the current user has explicit access to a file without checking security group membership is unacceptable.
I had a room full of developers ready to get training on the new features of SQL Server 2012. Spending 90 minutes figuring out how to attach the AdventureWorks databases on each of their laptops was not a good use of our time.
We have laptops running Windows 7 64-bit and SQL Server 2012. I downloaded the AdventureWorksLT2012 database to our network and the developers copied it to their laptops. I instructed them to copy the mdf file to their default data directory. Attempting to attach the database through SSMS resulted in the Access Denied message. THe only way to resolve the issue was to grant the individual developers access to the data directory even though they already had access through appropriate security groups.
on 5/7/2012 at 7:56 AM
I would like to submit this for re-opening because the behavior that you are describing has a bug. The problem is that my account is not being impersonated correctly. (my account DOES have access to the files I am attaching, but the attach still fails).
The problem is that my account has access to the files due to AD group membership. If I give my account direct access to the files, it works, If I rely upon the fact that I am a member of an AD group that has access to the files, it fails. So, I think the the impersonation that the tool is attemping is flawed -- it is not taking domain group membership into account.
on 5/1/2012 at 3:27 PM
This is expected behavior. We impersonate the attacher to validate that they have permissions on the files they are trying to attach to validate that they are not leveraging the service account to attach files they do not own.
Cornan The Iowan
on 3/2/2012 at 6:25 AM
Briefly, I am seeing something similar with CREATE ASSEMBLY FROM \\UNCpath\file.dll, leading to "ACCESS IS DENIED".
My point is that (like this problem) SQL Server is clearly not trying to access the destination file - something within SQL Server is concluding that "ACCESS IS DENIED", without report what.
The SQL Server developers should rewrite some of this code to clearly report (or at least log in SQL Server logs) the exact proximate cause for issuing the (ludicrously generic) "ACCESS IS DENIED" messages.
on 1/13/2012 at 5:10 AM
I also have this issue and have done a little investigating.
It seems that the database files must have not only permissions for the SQL Server account but also for the currently logged in user account.
When I give my user account full control on the database files the error goes away. Why does the user account need full control on these files as well as the SQL Server account? Furthermore, it is not enough for the user to be a member of a group that has full control, the user must explicitly be given permissions for some reason.
To further compound the problem, I have also found that performing certain operations on the database (including bringing it off-line) will reset the permissions meaning that I have to reset the full control permissions for the current user each time.
In reply to Microsoft - in order to reproduce have you made sure that the database you're trying to attach is NOT in the 'Database default location'? This issue only seems to happen for database files located outside this folder.
on 9/21/2011 at 8:08 AM
I was able to successfully reproduce a problem very like this problem, today. And, the workaround (starting sql management studio as administrator) also worked.
The situation arose, for me, because I initially had sql server running on my laptop as the network service user id (but I changed this on advice, because of permissions problems running someone else's power shell code under the sql agent).
After uninstalling sql server and reinstalling it, running as the local system user, I was not able to re-attach the database I had been working on. And, while I did get a clear error message that there was a permissions problem, there was no information as to why the permissions were a problem nor what I should do to remedy the situation.
Running sql management studio as administrator solved the problem for me (and seems less drastic than another suggestion I encountered, which was to turn off UAC notification).
on 4/7/2011 at 5:16 PM
More details on this bug have been reported here:
on 3/16/2011 at 6:27 PM
Reproduced using SQL2008 trying to restore a backup from one instance to another.
on 2/20/2011 at 11:20 AM
This is still an issues. We just set up SQL Server 2005 Developers edition on a Windows 7 (64 bit) machine - both are fully patched with SQL Server 20005 at SP4. The domain admins can not attach a database due to permssions:
Operating system error 5: "5(Access is denied)". (Micorsoft SQL Server, Error: 5120)
on 2/8/2011 at 5:59 PM
I can repro this. Win7, SQLEXPRESS, Domain user is sysadmin. Create DB. Detach DB. Move MDF and LDF to a new folder C:\Temp. Try to attach. Access Denied. Grant the current domain user full control over C:\Temp folder. Try to attach again. Success.
on 12/1/2010 at 7:15 PM
I actually encountered this tonight, moving databases from one drive where they had originally been created to another drive where I manually configured permissions. Server is 2008R2 Developer on Windows 7 Ultimate. Server runs as Network Service. I am logged on as a domain account with local admin on the box. Detach worked OK. I copied files to folder on new hard drive. Folder is owned by Administrators. I forgot to add Network Service to the new target, so I got Permission Denied when I tried to attach. Realizing my mistake, I configured new file copies with FULL CONTROL by Administrators and Network Service. Still permission denied. After reading this post, I added my account directly to the ACLs of the files themselves, and the attach worked.
It appears that the SQL Server is attempting to use the logged on user account to access the files before doing the attach and that this is what is failing if the user only has permission through a group. Hope this helps.
on 4/12/2010 at 11:35 AM
We attempted to reproduce this issue using Windows 7 Enterprise and SQL Server 2008 RTM, but were not able to (everything worked correctly). Could you please verify that the repro steps provided are accurate, and that this successfully repros in your environment?
SQL Server Program Management
to post a workaround.
Please enter a workaround.
on 6/10/2011 at 10:18 AM
Perfoming a "run as administrator" for SQL Server Management Studio corrected this issue for me. Also, disabling User Account Control corrected the problem as well.
on 4/7/2011 at 5:17 PM
On Windows 7, assign Read permissions to all folders and parent folders for the database file(s) to the MS SQL Server user, and assign Modify permissions to the database file(s) themselves. You do not need to run as Administrator if you have taken these steps.
Chris Shouts - Centare Group Ltd
on 3/23/2011 at 12:25 PM
You can workaround this issue by running SQL Server Management Studio as an administrator on Windows Vista / 7.
© 2014 Microsoft