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 ““. 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.

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

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

OK
——————————

The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',
                     @filename1='D:\MSSQL10.MSSQLServer\MSSQL\Data\DBName.mdf',
                     @filename2='E:\MSSQL10.MSSQLServer\MSSQL\LOG\DBName_log.ldf'

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

References
[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.