Database Attach Failure in SQL Server 2008 R2
By: Manvendra Singh | Comments (18) | Related: More > Database Administration
Problem
When you attach a database in SQL Server 2008R2 which was detached by a user with a different login you may get this error:
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.
- Read these other security tips
- Read these other tips about detaching and attaching databases
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.
View all my tips