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:
- Fire up SysInternals Process Monitor
- Open the 'Filter' menu
- Click 'Filter...'
- From the first dropdown select 'Path' in the second dropdown select 'contains'.
- For the value enter '\data'
- Click the 'OK' button to add the filter.
- Retry the operation
- Open up one of the NAME NOT FOUND entries with a Detail of Access Denied.
- Click the Process tab
- Note the process is sqlsvr.exe and the user is NT Authority\Network Service
- Navigate to the Data directory in Windows Explorer
- Click the Security tab
- Click the 'Edit' Button
- On the Permissions for 'Data' click the 'Add' button.
- For the object name enter 'Network Service'
- Click 'OK'
- Disregard any errors regarding not being able to set the permissions on files.
- Retry the operation in Sql Server Management Studio and it should work!
2 comments:
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.
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
Post a Comment