Saturday, July 3, 2010

CREATE FILE encountered operating system error 5 Access is denied

I have an MSSQL 2008 R2 installation and was trying to attach a DB from a non-R2 installation using SQL Server Management Studio (SSMS) and I was getting the following error:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDataBase.mdf'. (Microsoft SQL Server, Error: 5123)

Found plenty of Google results pointing me to suggestions about SQL Server 2000 and 2005 with instructions on how to add user permissions and which user account needed to be added.

Turned out that all I needed to do was to run SSMS as an administrator and the attach worked.

34 comments:

  1. I have resolved the problem with the following steps:
    1. Set OS permission on the .mdf file to full permission for 'Everyone'.
    2. Attach the database.

    ReplyDelete
  2. The problem on my side was caused by changing the Operating system to Windows 7. Using runas command solved it. Thanks!

    ReplyDelete
  3. Hi Udo! Thanks.
    (Set OS permission on the .mdf file to full permission for 'Everyone')

    ReplyDelete
  4. confirmed as a solution.

    ReplyDelete
  5. Yes Confirmed as Solution

    ReplyDelete
  6. Haaaa,, it's works.. thanks

    ReplyDelete
  7. Yeah..I too confirm as solution...
    Thanks

    ReplyDelete
  8. Confirmed as well. However, a puzzle that i can't answer: had 2 DBs to re-attach, 1st one worked like a charm, on 2nd got that same error. DBs in question have the same origin (SourceGear Vault) - [sgmaster] and [sgvault], and aren't any different in permissions or such. Also noticed that in the error message the .mdf file is specified along with "CreateFile failed" - why is SMSS (or SQL) trying to create a file which already exists!? If anyone has a clue, i'd appreciate it!

    ReplyDelete
  9. Thanks, that definitely was the fix for my team!

    ReplyDelete
  10. at first I was a bit wary of the domain name, but fair play, this sorted my problems (and made some support guy at DoubleTake's day a lot easier), so there is indeed some rocking!
    Tommy Vance would be impressed.

    ReplyDelete
  11. Open QL Server Management Studio by right clicking and saying "Run as Administrator".
    From: social.msdn.microsoft.com/.../16071cf8-b9ef-4

    ReplyDelete
  12. Excellent... It works! Confirmed!!!!!

    ReplyDelete
  13. Thanks a bunch! I took me a good 8 hours to track down this error.

    ReplyDelete
  14. How to set OS permission on an mdf file?

    ReplyDelete
  15. Right click on the Management studio then click on
    Run as Administrator then attach the database.
    Thanks!

    ReplyDelete
  16. Start SQL Server manager as Administrator (right-click on the program, choose "Start as administrator"): problem solved!

    ReplyDelete
  17. What is the .mdf file? The database?

    ReplyDelete
  18. I had this issue and all the solutions online was misleading. I had the solution here. Thanks you shared it too. dotnet-programming-solutions.blogspot.com/.../attach-database

    ReplyDelete
  19. Starting as administrator and set OS permission on the .mdf file to full permission for 'Everyone' is an excellent answer!!... it works terrific!... thanks a lot..

    ReplyDelete
  20. Solved my issue as well!
    In case there is a transaction log you need to change the security for this file, too!
    The reason for this issue in my case was that I detached the file with another user than the user I wanted to use to reattach.

    ReplyDelete
  21. In my case, this error was occurring when trying to Create a new database.
    I was already running SSMS as an administrator, so that was not the solution for my problem.
    Turned out I had to modify ACL on the Windows folder where the physical file for the database was to be created. I granted the SQL user account permissions to create files in my new database target folder.

    ReplyDelete
  22. Hi Udo,
    your solution is working properly,
    thanks in advance

    ReplyDelete
  23. Thanks Manish!!! Solution worked like a charm!!!

    ReplyDelete
  24. Other solution. Change the account for run SQL Services.

    ReplyDelete
  25. Hi All,
    I used Run as admin of SSMS. it worked for me also.
    Thanks all,
    Praveen Sagar Velpula

    ReplyDelete
  26. I have a problem is there any SqlServer Expert to solve it when i attached the database in sqlserver then it give the following problems and give the following messags

    CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WPS.mdf'. (.Net SqlClient Data Provider)

    ReplyDelete
    Replies
    1. Are you running SSMS as an administrator? Right click and select "Run as Administrator."

      Delete
    2. This comment has been removed by the author.

      Delete
  27. Dear all hello to every one
    i want to could attached the database in sqlserver in Network environment then it give the following message
    is there any body here to help me thanks
    CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WPS.mdf'. (Microsoft SQL Server, Error: 5123)

    ReplyDelete
  28. Yes but the folder which the mdf and ldf is exists this folder is read only when i remove it is again check by default read only

    ReplyDelete