Recently just for experimental puprose, I have relocated the default (C:…) data\log file paths in server settings to new location (G:\DBA).
Then I've moved the previously existing db's data\log files to the new location (G:\DBA) and restarted the SQL services. From then, whenever I'm creating DB, it is working as expected i.e., data\log files are getting created in new location(cool…).
But the problem is, I see all my previously existing DBs are in RecoveryPending state also got an error like Access denied.
Thinking that service account on which SQL is running (NT SERVICE\MSSQLSERVER) is not having access to new location(G:\DBA), I changed the logon account as Local System for SQL services in Computer Management console. Now I see all db's are good.
But, I want to run my sql services on NT SERVICE\MSSQLSERVER (default instance) service account. I tried by giving full access to G:\DBA\ for NT SERVICE\MSSQLSERVER, but no use. Please help me what all I have to do if I want to run my SQL services back on NT SERVICE\MSSQLSERVER service account. I'm beginner in this field. Please help me out.
Thanks in advance. . .
Best Answer
You have to change the service account in SQL Server Configuration Manager in order to have all required permissions applied to the service account.
Open Configuration Manager, select SQL Server Services in the treeview on the left, then open the properties window of the desired SQL Server instance.
Set the service account as follows:
Then open file explorer, navigate to the SQL Server data root and change the permissions on the properties window. Click the Advanced button:
Click the "Change permissions" button:
Then add the service account using the add button. If your computer is in a domain, make sure you select the local computer and enter
NT SERVICE\MSSQLSERVER:Make sure you check the "replace permissions on child objects" checkbox.