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

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 2

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 Micro$oft, you should be taking a quick look at these two pages:
http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites&referringTitle=Home
http://msftdbprodsamples.codeplex.com/wikipage?title=Installing%20Databases&referringTitle=Home
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 3

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 4

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.

Answer 5

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 repro it over and over again, so I have to conclude that it is the answer.

Answer 6

I used Win XP and SQL 2005. After installing Win 7 and SQL 2008, I had problems with attaching my previous DBs and Creating new DBs.

somebody told me: On a NTFS partition you should tick the "FILESTREAM" option while installing SQL. So I reinstalled it with the new option. After instalation I checked if I could attach the DataBases. The first one was "MyWord" and it attached with no problems. I didn't check the next 2 as I though there would be no problems either.

I wanted to work on my project today and I tried to attach the other DBs but I found it imposible :( Also the priviously attached database (myWord) has become Read-only. I can attach/detach it but when it is attached I see "(Read-only)" infront of its name in the object Explorer. The strange part is that I get 2 different errors while trying to attach the next 2 DBs. Even I can copy and paste both of them to other drives and the "read only" attribute is not checked in the property page, I get these two error messages:

1- for the "verb" DB:

Attach database failed for Server 'CHAKAVEH-PC'.  (Microsoft.SqlServer.Smo)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

---

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Database 'verb' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. (Microsoft SQL Server, Error: 3415)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3415&LinkId=20476

2- for the "WSD" DB

Attach database failed for Server 'CHAKAVEH-PC'.  (Microsoft.SqlServer.Smo)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

---

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Unable to open the physical file "C:\DB\WSD.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

Can you please help me? Arian

PS I can creat new DBs with no problems now

Answer 7

It usually means your SQL Server service account has read only access to the file

How can I change it to full access?

Are you sure you didn't change the folder permissions in between logging in as yourself and logging in as sa?

I am not sure! Acctually I'm new to SQL and I geuss I don't undrestand you right. If I am not mistaken, by sa you mean "server acount". How can I log in as sa? and How could I change the folder permissions?

(After the problem persisted, I read many forums that had some guides on using "accesschk" and "cacl"-I used them, the problem is not solved though)

If you detach and try again, does it still have the same problem?

I detached "myWord" and now I can't attach it :( I get the same Error message as the error for the WSD database, for all the three data bases :((

Please Help me.... tnx