Learn more about SQL Server tools

   
   















































Database Attach Failure in SQL Server 2008 R2

MSSQLTips author Manvendra Singh By:   |   Read Comments (16)   |   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: 

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:

sql server attach database error 5123

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.

sql server sp_detach_db command

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.

sql server create database with attach command

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.

windows permissions

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.

sql server create database for attach command

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


Last Update: 2/24/2012


About the author
MSSQLTips author Manvendra Singh
Manvendra Signh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, September 19, 2014 - 7:58:59 AM - Shubhangi Read The Tip

Thank You So much for your post. It really help mw in my project.

Thank You,


Monday, June 16, 2014 - 11:30:50 AM - theo Read The Tip

try to execute both atach and detach under the SQL Server Authentication

 

 


Sunday, May 25, 2014 - 12:22:11 PM - Jim Read The Tip

Thanks for the tip. Change the permission on the db and log file to give myself full permissions through the security tab and it loaded just fine.


Thursday, May 15, 2014 - 2:45:41 PM - Mortada Yeassen Read The Tip

I had faced this Problem Also to reattach DB to same Server After De_attach it

Just do this

Copy mdf file and log file to a new folder with any name "ensure that Everyone has permission (Full control on it)"

then you can Attach it.


Saturday, May 10, 2014 - 3:21:35 AM - Dimitris Read The Tip

Hello,

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.

 


Wednesday, April 02, 2014 - 4:54:06 PM - Mark Read The Tip

Immediately solved my problem.  Thank you for sharing the details.

 


Sunday, February 09, 2014 - 11:40:52 PM - Sumit Dhari Read The Tip

Thank you so much for Post

It really helped

Sumit...


Tuesday, February 04, 2014 - 2:41:30 AM - satish Read The Tip

Thank you Manvendra Signh !

Nice tip.


Wednesday, July 31, 2013 - 11:55:09 AM - Ray Read The Tip

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 :)

Ray


Friday, December 28, 2012 - 8:21:22 AM - NAVEEN Read The Tip

Awesome i did it....


Monday, September 17, 2012 - 2:30:51 AM - Ramesh Read The Tip

Thank U Very Much...


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.


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?


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.


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.


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




 
Sponsor Information