locked
Moving DB's to new location, running SQL Server with domain user, does MSSQLSERVER need permissions to folders? RRS feed

  • Question

  • Hello,

    I have a setup where the SQL services are running under a domain account and the databases/logs, including system, are moved to a different location. The domain user service account has permissions set for this new location, but I'm wondering if the MSSQLSERVER account also need to be assigned permissions? 

    Friday, July 26, 2013 1:02 PM

Answers

  • NT Service\MSSQLSERVER is the per-service SID for a default instance of SQL Server (a named instance will have a different SID). 

    SQL Server will work correctly if either the service account or the per-service SID has the permission.

    As a best-practice you should assign the rights to the NT Service\MSSQLSERVER per-service SID.  This will ensure that you don't have to change any permissions if you change the service account, and prevent the accumulation of privileges to the domain account.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by PolishPaul Friday, July 26, 2013 4:36 PM
    Friday, July 26, 2013 2:08 PM

All replies

  • I haven't ever had to add the MSSQLSERVER account on the folders when moving databases.

    Friday, July 26, 2013 1:19 PM
  • Are you able to restart the SQL Server services once they are moved to new location?

    When you say different location, is this new domain or new server?

    It is ideal to grant necessary permissions for the service account that is used for SQL Server services.


    Satya SKJ, Moderator - SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.

    Friday, July 26, 2013 2:03 PM
  • NT Service\MSSQLSERVER is the per-service SID for a default instance of SQL Server (a named instance will have a different SID). 

    SQL Server will work correctly if either the service account or the per-service SID has the permission.

    As a best-practice you should assign the rights to the NT Service\MSSQLSERVER per-service SID.  This will ensure that you don't have to change any permissions if you change the service account, and prevent the accumulation of privileges to the domain account.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by PolishPaul Friday, July 26, 2013 4:36 PM
    Friday, July 26, 2013 2:08 PM
  • What version of SQL Server are you using? There is a topic in Books Online for SQL Server 2012 Configure File System Permissions for Database Engine Access http://msdn.microsoft.com/en-us/library/jj219062.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, July 26, 2013 2:30 PM
  • I'm using SQL Server 2012 ENT. Thanks for the link Rick.

    Dave, that's interesting. I was just experimenting with two servers and found out that the MSSQLSERVICE account is sufficient. I had the domain service account permissions set to Modify on server A and Full on Server B and I could not figure out why the services still started on server A. Turns out that when i installed SQL on server A using a configuration file, i specified default directories for data/log/backups. This assigned the MSSQLSERVICE account to those folders, thus the service started.

    I didn't see this mentioned in the security best practices, but I see the benefit of using MSSQLSERVICE for folder permissions instead of domain users as you explained.

    Thank you all!

    Friday, July 26, 2013 3:48 PM
  • Follow up question:

    So now I have the domain users running the services and the folder permissions are given to MSSQLSERVER. But what about the SQLSERVERAGENT user? Do i have to give that user any NTFS permissions? For example the backup folder? I looked on one server which is just using local accounts and the installer only provisioned the MSSQLSERVER account NTFS permissions on the folders. 

    Friday, July 26, 2013 5:52 PM