When you attach a database in SQL Server 2008R2 which was detached by a user with a different login you may get this error:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)
This is a screenshot of the error:
In this tip we will cover how this can be resolved.
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.
Find the below steps to resolve this issue.
Steps
Lets consider that someone detached a database and you have to attach it to bring the database online. For this example, we will use two logins to perform this activity. I will detach the database using the first login and then try to attach it with the second login. Not that both accounts are part of the local administrator group on the server.
Step 1 Here I detach the database named "stats1" with my login account.
Step 2 Now I have logged in with the second account and will try to attach the database, but during this process I am getting the below error.
Step 3 Right click on the "stats1.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 "stats1.mdf" file because that was the account that was used to detach the database.
Step 4 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.)
Step 5 Now try the attach again and it should work.
The permissions prevented the files from being modified should they reside in a directory that has open permissions. For example, if the permissions are not set and the operating system permissions on the database directory are set to Full Control for everyone any account that has access to that directory can delete or modify the database files even though they may not have SQL Server permissions to modify the database itself.
Next Steps
If you run into this issue, use the above steps during a detach and attach for a database.
Also check the security permissions on data, log and backup files during operations like detach, attach and backups.
I tried to attach my database which was detached i dont know how? but i was getting same error 5123. i saw your solution and checked my mdf file permission. for my database mdf file 3 groups have full permissiin from which administrative is one. again i tried to attach database by opening server managemnt solution as administrative. but again i am having the same problem.
please suggest some solution. i am trying from last 5 days and i dont have much knoweldge about databse.
You are half wrong. The account you need to add with full permissions is the Sql Server User Account. Adding any other user account including the currently logged in user does not work.
I am pretty sure this is a Windows 2008 and Windows 2008R2 issue not a SQL issue. You would have the same problem if you tried to Detach/Attach databases from SQL 2005.
As Dhwani mentioned the solution is to set the appropriate file and folder permissions for the Domain Service Account that SQL Service is running under. You are running SQL under a service account right :)
I faced this problem a week ago and one more solution is that - you need to go to properties of the folder/files and give full access to SQL service account and the problem got resolved for me.
Saturday, February 25, 2012 - 2:03:03 AM - Sherbaz Mohamed C P
Although it may not be practical for all environments or situations, a method we use to avoid this is to detach the database(s) while logged in with the sa login. That will leave file permissions intact.
Friday, February 24, 2012 - 4:51:11 AM - Sherbaz Mohamed C P
I have figured out an alternate methord to resolve this issue. Suppose we have a number of mdf and ldf files, may be more than 100. It will be difficult to change the above settings for all the files in one go. So I have figured out this methord for doing the same.
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.
