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. enter image description here 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. enter image description here

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

up vote 8 down vote accepted

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:

Configuration Manager

Then open file explorer, navigate to the SQL Server data root and change the permissions on the properties window. Click the Advanced button:

Folder properties

Click the "Change permissions" button:

Advanced permissions

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:

Find user

Make sure you check the "replace permissions on child objects" checkbox.

Edit permissions

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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