1

Default folder is typical C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA. But C: drive on server is almost full.

So, in restoring an 11gb database from another server, I decided to use the L: drive on the same server.

Tried to restore to L:\SQL\Data (a folder I created). Access Denied.

Some quick Googling later, I see that the SQL Server service is started by NT SERVICE\MSSQLSERVER, so I make MSSQLSERVER the owner of the L:\SQL\Data folder and give it full permission to the folder.

Try to restore again. Access Denied.

Hm. Try to create NEW database in same location. Access Denied.

Create new database in default folder, detach, move mdf/ldf to L:\SQL\Data, making sure all folder and file permissions and ownership are set to MSSQLSERVER, then attach using CREATE DATABASE ... FOR ATTACH. Database created in read-only mode.

Try to change to read/write mode. Access Denied.

Create a folder datax in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL and give it appropriate permissions/ownership. Create a new database there. It worked!

Move back one folder and created a folder data in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER and give it appropriate permissions/ownership. Create a new database there. Access Denied.

Create a folder C:\temp\data and give it appropriate permissions/ownership. Access Denied.

SQL Server service has been restarted. Server has been restarted. Same results.

This last bit with xp_cmsshell might be a bit of a diversion...

So... tried EXEC xp_cmdshell 'md l:\data'; Message back is: "An error occurred during the execution of xp_cmdshell. A call to 'CreatePipe' failed with error code: '5'."

Changed permissions on root of L:, tried again, same result.

EXEC xp_cmdshell 'dir c:*.*'; Same result.

EXEC xp_cmdshell 'whoami /all'; Same result.

Anyway, bottom line is, everything I've read out here about getting an "access denied" when restoring/creating db is because of folder permissions. But best I can tell, those are correct. I even tried some differences with permissions being inherited versus explicit.

A couple links I've come across would indicate that there is no inherent restriction on putting data/log files anywhere I want, i.e., outside the default location.

I haven't tried changing the service account for the SQL Server service because I don't have that level of freedom on this server.

Also, this is from SSMS on both local machine and directly on server, and always connected under the context of an administrator on the domain.

  • Look at the accepted answer and another one few posts below. social.msdn.microsoft.com/Forums/sqlserver/en-US/… – McNets Dec 12 '18 at 19:01
  • Thanks. I went and took a look, but not really anything that resulted in getting over the hump. I've actually given up on this and moved on. I was working with a server with very limited free space and was tired of shifting things around. – John Chase Dec 13 '18 at 23:51
2

I guess the first thing I would do would be to make sure that the permission are set on the correct account. Run : select servicename, service_account from sys.dm_server_services to get the account on which the permission should be set.

  • servicename service_account ------------------------------- -------------------------- SQL Server (MSSQLSERVER) NT Service\MSSQLSERVER SQL Server Agent (MSSQLSERVER) NT Service\SQLSERVERAGENT (2 rows affected) – John Chase Dec 13 '18 at 12:24
0

Since I never manage to handle the GUI to set file/folder permissions correctly, I use ICALCS.EXE, below example sets permissions on files to a virtual service account for an instance named B:

icacls R:\SqlData\a\x.mdf /setowner MSSQL$A
icacls R:\SqlData\a\x.mdf /grant MSSQL$A:F
icacls R:\SqlData\a\x_log.ldf /setowner MSSQL$A
icacls R:\SqlData\a\x_log.ldf /grant MSSQL$A:F

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.