SQL Server Management Studio: Database Attachment Failed

Question

I have publish my website in IIS7. It is running in DefaultAppPool. The Identity of this app-pool is set to LocalSystem. The IIS is running and I can run the application from IIS without any problem.

Now I want to attach the database placed in inetpub's application folder:

C:\inetpub\wwwroot\EMSApplication\App_Data\ASPNETDB.mdf

I have added new logins in the SSMS which are: NT AUTHORITY\NETWORK SERVICE and IIS APPPOOL\DefaultAppPool.

But when I am trying to attach that database in the SSMS I am getting follwoing 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 'C:\inetpub\wwwroot\EMSApplication\App_Data\ASPNETDB.MDF'. (Microsoft SQL Server, Error: 5123)

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

Answer 1

I struggle with this sometimes when moving .mdf/.ldf's between computers. Often I have to fiddle with the file permission/ownership on the database files themselves for it to work.

Try giving the user account for SQL Server full access permissions to the ASPNETDB.MDF and if you have it the ASPNETDB.LDF file.

Also, the SQL Server account might not have access to read the C:\inetpub\wwwroot\EMSApplication\App_Data\ directory. You may have to fiddle with permissions here too.

Answer 2

I had this issue and none of the solutions online helped. Eventually I found the solution - run SSMS as Administrator.