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!
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.
ReplyDeleteI 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.
ReplyDelete- Kris