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!

2 comments:

Anonymous said...

I would have changed the service "Run As" account. Don't run SQL as an admin but rather a dummy user without any good privvies. You can get a lot more granular with the run as account and chances are your DEV env will look more like the prod env.

Anonymous said...

I had been arguing with my close friend on this issue for quite a while, base on your ideas prove that I am right, let me show him your webpage then I am sure it must make him buy me a drink, lol, thanks.

- Kris