Advertisements
Home > Operating System Errors, SQL 2008, SQL Errors, Troubleshooting > CREATE FILE encountered operating system error 5(Access is denied.)

CREATE FILE encountered operating system error 5(Access is denied.)

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

——————————
BUTTONS:

OK
——————————

If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Advertisements
  1. May 4, 2010 at 11:27 am

    You can see this when you dettach a database in SQL2005 or higher, the permission on the db files is chenged in that way that only the current user can attach the database again. If someoen else try to attach this Db than he encounters the error 5 system error.

  2. May 4, 2010 at 11:51 pm

    Hi Ludo,Yes I noticed that, until now I never had to attach file that someone else deattached. So it was new to me, the reason I didn't talk about it in the post was because I didn't know if it was by design on an issue in my configuration :).It was still on my to-do list to research it, thanks for confirming it for me.- Mohit.

  3. May 25, 2011 at 8:27 am

    Thanks Ludo for the useful info

  4. October 17, 2012 at 9:11 am

    I had this issue and all the solutions online was kind of misleading to my issue. I have the solution here.
    http://dotnet-programming-solutions.blogspot.com/2012/10/attach-database-encountered-operating.html
    The solution was to Run SSMS as Administrator.

    • October 18, 2012 at 8:44 pm

      Hi Habi,

      This is interesting, since the permission to control the file is for SQL Server service account. Maybe for the initial attach functionality it required your elevated permissions. If you have the SQL Server Errorlogs available can you see if you have Broken Token errors; these are normally associated with the User Access Control (UAC).

      Thanks for sharing your solution :).

      Mohit.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: