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