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.
Best Answer
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.