Monday, October 11, 2010

Access Denied on Adding a Database

Today I ran into a problem where I couldn't run a create script to create a local database in SQL Server Management Studio 2008 running on 64 bit Windows 7 Professional.  This is the error I received:

Msg 5123, Level 16, State 1, Line 1
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.SQLEXPRESS\MSSQL\DATA\xxx.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This is how it was fixed on my machine:

  1. Fire up SysInternals Process Monitor
  2. Open the 'Filter' menu
  3. Click 'Filter...'
  4. From the first dropdown select 'Path' in the second dropdown select 'contains'.
  5. For the value enter '\data'
  6. Click the 'OK' button to add the filter.
  7. Retry the operation
  8. Open up one of the NAME NOT FOUND entries with a Detail of Access Denied.
  9. Click the Process tab
  10. Note the process is sqlsvr.exe and the user is NT Authority\Network Service
  11. Navigate to the Data directory in Windows Explorer
  12. Click the Security tab
  13. Click the 'Edit' Button
  14. On the Permissions for 'Data' click the 'Add' button.
  15. For the object name enter 'Network Service'
  16. Click 'OK'
  17. Disregard any errors regarding not being able to set the permissions on files.
  18. Retry the operation in Sql Server Management Studio and it should work!

Monday, October 4, 2010

Unable to rename database in SQL Server Management Studio 2008

Today I wanted to rename a SQL Server 2008 database so I would have a rollback if my create from script went bad.

Upon rename SQL Server Management Studio 2008 gave me the error 'The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)'

I executed this in a query window connected to the master database:
sp_who2

Looking at the DBName column in the results for each match of the db I was trying to rename I executed 'kill xxx' where xxx was equal to the SPID of the row.

After doing this for all the rows rename succeeded immediately.