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

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.

Answer 2

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

Answer 3

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!

Answer 4

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.

Answer 5

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.

Answer 6

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