NT SERVICE\MSSQLSERVER cannot be found to add as user for file permissions

Question

I need to give the NT SERVICE\MSSQLSERVER account permissions on a folder to be able to move and rename some files. I'm getting an access denied error. From the SQL Configuration tool, the services is set up like the below image. What else has to be done to make this appear as a user account?

Answer 1

What else has to be done to make this appear as a user account?

Nothing. And it doesn't matter what your service account is. The SQL Server service always has privileges assigned to the per-Service SID "NT Service\MSSQLSERVER".

If the files are on the SQL Server, just add permissions for this account:

And if the files are on a remote share, give the permissions to the machine account instead, eg <YourDomain>\<YourServer>$.

Answer 2

I was able to sort out the problem. Apparently the server name was changed after SQL Server was installed. We had to update the server name.

SELECT @@SERVERNAME AS 'Server Name' - showed the old server name. We ran the following to update the DNS in SQL server:

sp_dropserver 'OLD-SERVER-NAME';
GO
sp_addserver 'NEW-SERVER-NAME', local;
GO

After which we restarted the DB server and verified that the name was correct. After this, I could then pick the new server name as the location for checking for user accounts and with that location selected, the system was able to see the MSSQLSERVER and SQLSERVERAGENT virtual user accounts, and have them added to directory permissions.

Answer 3

Just to add some other possible sources of this error:

• The account isn't always named NT Service\MSSQL, if you have a named instance it is named NT Service\MSSQL$<your instance name>. You can list all service accounts to verify which ones you have by running get-service | foreach {Write-Host NT Service\$($_.Name)} in powershell. For example mine was NT Service\MSSQL$SQLEXPRESS2019
• in the final step when you enter the name of your service account, make sure that "select this object type" includes service accounts (mine didn't by default).
• in the final step when you enter the name of your service account, make sure that "from this location" includes your local machine (mine didn't by default).

Answer 4

Top answer doesn't say how to find the MSSQLSERVER user. If you go to the folder you're trying to grant permissions to:

1. Right click on the folder, click properties, click edit next to 'To change permissions, click edit'.
2. Click 'Add...'. For object types, just select all of them.
3. For locations, I can't remember which one but I selected either my system name or 'Entire Directory'.
4. Then once I typed in MSSQLSERVER it did pop up, and then I needed to edit the permission for my service user by clicking edit again and selecting 'Full control'.
5. Then my service account was able to add files to my data folder.