6

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?

enter image description here

enter image description here

enter image description here

0

4 Answers 4

5

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:

enter image description here

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

3
  • That's what I would expect - but when I hit edit, and then try to add the MSSQLSERVER or the SQLSERVERAGENT user it is unable to find a user. I've tried multiple searches (nt service, sql, ms, server, etc.) all with the same result: Name Not Found. I have added a couple of screen shots of the SQL Config manager, and the security add user lookup name not found.
    – mrSpear
    May 13, 2021 at 14:33
  • @david - regarding the remote share solution you have provided, is <YourDomain>\,<YourServer>$ the SQL server engine or the SQL server agent?
    – variable
    Feb 17 at 8:32
  • There's only one machine account. All the virtual service accounts access network resources using the machine account. Feb 17 at 13:44
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.

2

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

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.
3
  • Plese edit your response, look at the help and use the list markdown. Jul 24, 2023 at 20:19
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jul 24, 2023 at 20:19
  • In my case, the "select all objects type" was the answer
    – luiscla27
    Dec 15, 2023 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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