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

Recently I upgraded my development machine to Windows 7 and moved all daatabase files to it as well. When I tried to attach one of the database to SQL Server 2008, i got the following error.

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

The error is very clear that it has everything to do with file system permissions on the database file. I searched around for answers to why SQL Management Studio is having issue although I am logged in as administrator on my machine. All the search results hinted that account under which SQL server is running does not have enough previlleges. So I was like so I just need to set appropriate permissions on MDF and LDF or any other database related files. The most dreaded suggestions that came across were people suggesting to give Full Control to the user account on the database folder(s). I am not going to go into discussion about this here why this is just bad. Lets just say that I am not a big fan of giving blanket full control permissions to user accounts to sensitive files without understanding what it means.

My SQL server instances were running under Network Service account. So I looked at the properties of the database folder and database files. And changed the permissions. Well that did not work. Since the error is very clear about the problem, it was time to do some old school debugging of problem. Since the error is related to file system, so it was time to fire up my good old Process Monitor tool. Now I was going to watch for Access Denied error when my database file was going to be accessed.

What user account is used to access the database file?

If you can find the answer to this question, then all your problems are solved. So this is where Process Monitor is going to help. Start Process Monitor and try to attach the database file. Now capture the results from Process Monitor. You definitely will want to filter the results to include the entry related to your database file only. See the following image where the last entry for my database file has STATUS code of Access Denied. This is what I was looking for. Now double click on this entry and it will bring up details about that I/O operation. The following image shows the account that is being Impersonated to access this file. Yes, impersonation. When you start SQL Management Studio, it asks what kind of authentication you want to use. If you select currently logged in user or Windows Authentication that means SQL Management Studio is going to take all action on your behalf when accessing operating system resources.

Fix It

Now you have two options to fix your problem.

• If you are going to use Windows Authentication for SQL Management Studio, then you need to fix permissions on the database files to include that user with appropriate rights.
• If you are going to use SQL Authentication, then make sure that account under which your SQL Server (and not SQL Management Studio) runs has correct set of permissions to access the physical files.

Making the above change should fix your problem and you will be able to attach the database without any errors.