Database Attach Failure in SQL Server 2008 R2

By: Manvendra Singh   |   Read Comments (7)   |   Related Tips: 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
  • Upgrading to SQL Server 2008 Using Detach and Attach Operations
  • Limitations when attaching a SQL Server Database

Last Update: 2/24/2012

About the author

Manvendra has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Friday, February 24, 2012 - 4:51:11 AM - Sherbaz Mohamed C P

Read The Tip

Hi, 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. I have posted the steps in my blog in detailed at http://www.sherbaz.com/2012/01/access-denied-error-while-attaching-the-database-files-move-from-windows-2003-server-to-the-new-sql-instance-on-windows-2008/ Hope this helps. Feel free to correct me if I am wrong and help me. Thanks Sherbaz

Friday, February 24, 2012 - 1:15:27 PM - Larry Edlin	Read The Tip
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.

Saturday, February 25, 2012 - 1:46:43 AM - Manvendra	Read The Tip
If you will use sa account to detach then you need same account to attach the db. same situation. But mostly in production we disable the sa account during lockdown or during implemention security best practice.

Saturday, February 25, 2012 - 2:03:03 AM - Sherbaz Mohamed C P	Read The Tip
Yes. sa account is disabled in most of the environments. May be we should consider using some temporary sql authentication account. Right?

Wednesday, February 29, 2012 - 12:02:17 PM - Dhwani	Read The Tip
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.

Monday, September 17, 2012 - 2:30:51 AM - Ramesh	Read The Tip
Thank U Very Much...

Friday, December 28, 2012 - 8:21:22 AM - NAVEEN	Read The Tip
Awesome i did it....

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. SQL Server Data Tools - Got questions? Get the answers here!