Database Attach Failure in SQL Server 2008 R2

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

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, October 11, 2017 - 2:31:35 AM - Simrat Back To Top (67182)

 Hello Manvendra,

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.

 


Monday, November 16, 2015 - 3:18:51 PM - hub Back To Top (39078)

Thanks.


Friday, September 19, 2014 - 7:58:59 AM - Shubhangi Back To Top (34623)

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 Back To Top (32264)

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

 

 


Sunday, May 25, 2014 - 12:22:11 PM - Jim Back To Top (31933)

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 Back To Top (30797)

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 Back To Top (30718)

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 2, 2014 - 4:54:06 PM - Mark Back To Top (29949)

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

 


Sunday, February 9, 2014 - 11:40:52 PM - Sumit Dhari Back To Top (29382)

Thank you so much for Post

It really helped

Sumit...


Tuesday, February 4, 2014 - 2:41:30 AM - satish Back To Top (29323)

Thank you Manvendra Signh !

Nice tip.


Wednesday, July 31, 2013 - 11:55:09 AM - Ray Back To Top (26074)

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 Back To Top (21170)

Awesome i did it....


Monday, September 17, 2012 - 2:30:51 AM - Ramesh Back To Top (19525)

Thank U Very Much...


Wednesday, February 29, 2012 - 12:02:17 PM - Dhwani Back To Top (16217)

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 Back To Top (16159)

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 Back To Top (16158)

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 Back To Top (16153)

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 Back To Top (16148)

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















get free sql tips
agree to terms