Friday, July 20, 2012

Attaching Databases to Local SQL Server Data Engine Instance

Don't you hate it when you can't figure out what permissions you need to set on a file or folder to get an application to work? It's especially annoying when all the security settings seem to be in place but nothing seems to work. The problem I am about to describe is documented on Microsoft Connect but has been closed, in my opinion, incorrectly.

Reproducing the Problem

For me, and the BI development team I work with, the problem reared its ugly head when we were ready to begin a class on the new features in SQL Server 2012. I had downloaded the AdventureWorks sample databases to the local network so the developers could save time copying them to their machines. Our computers all run Windows 7 and have SQL Server 2012 64-bit. Additionally, our security policy does not allow us to have administrative access to our machines on a standard basis.

The developers copied the mdf files to the default SQL Server data directory and then tried to attach the databases. That should have been the end of it, but no, we got one these two error messages instead.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\<SQL Data Path>\AdventureWorksLT2012_Data.mdf'. (Microsoft SQL Server, Error: 5123)
User 'guest' does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)

Troubleshooting

I chased the issue for over an hour hunting for the right ACLs and whatnot. I looked into both of the following.
  • Made sure the MSSQLServer account had access to the data directory. The SQL Server installer got that one right.
  • Checked that the users had access to the data directory even though it shouldn't matter. They all had access via an appropriate security group.
The users each copied the file to their data directories so their permissions shouldn't be an issue. It still bothers me that the user's permissions should have anything to do with it. The SQL Server service account, yes; but the SSMS user account, no.

Workaround

Somehow someone in the room, I don't remember who, ran SSMS as an administrator and it worked.

I don't like the resolution and, if you agree, please vote for the connect issue linked at the beginning of this article. A comment wouldn't hurt there either. If you side with Microsoft on this issue, please explain in the comments. I am open to other ideas but I admit that I don't understand why everything about this problem goes against all that I have learned about Windows security in the past decade and a half.

Let me know what you think in the comments!

4 comments:

  1. I have exactly the same problem.. Did the same troubleshooting, and even tryed to run it as admin but none of them worked :s did you find another solution?

    Greetings

    ReplyDelete
  2. I had a similar problem recently where another DBA on the team couldn't restore a database. It turns out that the folders the database files went in were owned by me and he couldn't access them even though he was an administrator on the server. Changing the folder owner to the local Administrators group fixed that issue.

    ReplyDelete
  3. It's a load of bullshit if you ask me...I have the same problem and running as an administrator does not work...it tells me that 'dbo' does not have the permission

    ReplyDelete
  4. @Unknown: Are you running SSMS on the same machine that you're trying to attach the databases on? This whole scenario is a pretty big mess if you ask me.

    ReplyDelete