[Fix] - Error 5123 CREATE FILE encountered operating system error 5A Access denied
Scenario :
- When I detach a database file (type .mdf) I encountered the following problems:
1.I opened MS SQL Management Server Studio and detached my database file successfully from a connection called Workhorse.
2.And I need to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal which was successfully shared.
3.However, when I tried reattaching the database file, I got this error:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'
- And the database file and log file (ldf) exist in the correct directory so I don't know what happened.
Reason :
- This error occurs because two different logins performed 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 the database files, the owner becomes the person who did the detach command, so as to resolve the issue we need to change or add the other login as the owner of the mdf and ldf files.
Fix1:
- Here is the fix to resolve this issue by simply opening the “SQL Server Express Management Studio" by executing it as “Run as ADMINISTRATOR”.
Fix2 :
- Sometimes to resolve this issue by setting the Operating system permission on that .mdf file to full permission and choosing the option as ‘Everyone’ in new server, we can solve this.
Fix3 :
- It may be a security issue at cases so, it’s better to give permission to the SQL Server account.
- If we are have already running as an administrator, make sure the user we are using has the proper server roles.
1.First step is that; we need to Login as “sa” (System Administrator (sa) Login)
2.Expand the Security folder
3.Expand the Logins Folder
4.Right click on the user you would like to use
5.Select Properties
6.Select Server Roles
7.Select all the server roles
8.Click OK
9.Restart SSMS
10.Login with the modified user
Fix4 :
- Here is the another fix to resolve this issue:
- Go to the xx.mdf and xx_log.ldf files where database is located and give “Everyone” user to full access right for these files.
Fix5 :
- Close Visual Studio and try re-attaching the files.
- This is the one we have to tried first to make sure that no other process is blocking the mdf file.
- Then we need to check the permissions and if we are using Vista, try running MSSQL Server Management Studio as Administrator.
Fix6 :
- Simply sometimes Windows authentication trick works to resolve this issue.
Fix7 :
- Initially turn off the User Access Control(UAC)
1.Start->Run->type msconfig -> choose Tools Tab -> find the option to Change or Disable UAC ->click done.
2.hen reboot the attached db.
Fix8 :
- Right click on the ".mdf" & .Ldf files in an SQL Server and select properties and assign the privileges of the login who is going to attach with full control.
- Then Connect the SSMS (SQL Server Management Studio) with Admin privileges and attach database.
Fix9 :
- Since there are incorrect number of spaces in the supplied path, so, it is not matching the folders tree.
- Sometimes , Sql server won't create an non existing path.
Fix10 :
- Here is simple way resolve this issue : copy your --.MDF, --.LDF files to pate in this location for 2008 server C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
- In Sql server use the ATTACH and select same location path for add the files.
Fix11 :
- After verifying the user, the service was running as, running MSSMS (Microsoft SQL Server Management studio) as local and domain
- administrator, and checking folder permissions, I was still getting this error. My solution?
- Folder ownership was still maintained by local account.
- Properties > Security > Advanced > Owner > (domain/local user/group SQL services are running as)
- This resolved the issue for me.
Fixes are applicable to the following versions of Sql Server :
- Database Attach Failure in SQL Server 2008 R2
- .mdf Attach Failure in SQL Server 2008
- .ldf Attach Failure in SQL Server 2008 R2
- Access denied error in SQL server
Related Error Tags :
- .mdf Attach / Detach Failure SQL server
- User control setting in SQL Server
- Administrative privilege in SQL Server
- Authentication setting in SQL server
