Home Dashboard Directory Help
Search

Access Denied attaching a database when permissions are inherited by jmarsch


Status: 

Closed
 as By Design Help for as By Design


24
0
Sign in
to vote
Type: Bug
ID: 539703
Opened: 3/5/2010 4:41:53 PM
Access Restriction: Public
3
Workaround(s)
view
6
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by daniel.john.wolf 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.
Posted by Albert Hajrizaj 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.
Posted by jendonl 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.
Posted by tfang_moe 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.
Posted by 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.
Posted by jmarsch 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.
Posted by Microsoft 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.

Thank you.
Posted by 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.

Posted by elziko 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.
Posted by rdmil 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).
Posted by Chris Moschini on 4/7/2011 at 5:16 PM
More details on this bug have been reported here:
http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database/5589045
Posted by phead2 on 3/16/2011 at 6:27 PM
Reproduced using SQL2008 trying to restore a backup from one instance to another.
Posted by AspenRoot 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)
Posted by JasonRoth 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.
Posted by jkeimig 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.
Posted by Microsoft on 4/12/2010 at 11:35 AM
Hi,

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?

Thanks,
David Cohen
SQL Server Program Management
Sign in to post a workaround.
Posted by m_mcghee 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.
Posted by Chris Moschini 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.
Posted by 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.