What permissions are required for SQL Server to run as a (active directory) domain user

Question

I wish to switch the SQL Server 2005 service from running as a local user to running under a domain user (mainly so I can make backups to domain-permissioned shares). I can't seem to find a definitive list of the windows permissions that I should grant that domain user, does anyone know where I can find a list?

Alternatively, is there a local group on the SQL server box I should make the domain user a member of?

Thanks in advance,

-James

Solution 1

This is what I did:

1. Create a new domain user named sql_user, granting logon to the SQL Server target machine
2. On the target SQL Server machine, using the users control panel add the domain user to the group SQLServer2005MSSQLUser$HOST$MSSQLSERVER (beware there is a similar group named: SQLServer2005MSFTEUser$HOST$MSSQLSERVER which it seems the user does not need to be a member of)
3. Grant sql_user the ability to lock pages in memory if using the AWE extensions, see also: MSDN article
4. Change the SQL Server service to startup as the new domain user in the service control panel
5. Give sql_user write permission to the c:\program files\microsoft sql server directory and all subdirectories
6. Give sql_user write permission to the datafiles for all of your databases
7. Restart the database service
8. Check the Windows Error log for any errors

Solution 2

No. I don't think you were looking at the right section.

The section of interest would be the one that indicates the specific permissions that need to be granted to the user. I just had to do this for a Windows 2008 MSSQL installation and to run it anything other than a domain admin these were the required permissions:

*Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

*Bypass traverse checking (SeChangeNotifyPrivilege)

*Log on as a batch job (SeBatchLogonRight)

*Log on as a service (SeServiceLogonRight)

*Replace a process-level token (SeAssignPrimaryTokenPrivilege)

I realize this may be slightly different in 2005 though so here's what I would suggest.

1. Create a simple regular user in the domain e.g DOMAIN\USER.
2. Before adding it to any groups, assign the above permissions to it via GPO under Computer Configuration > Windows Settings > Security Settings > Local Policies/User rights assignment
3. Do a gpupdate /force as admin so the policy is refreshed. Then try running the SQL service with that account i.e. DOMAIN\USER. If it doesn't work, add the additional permissions indicated in the link by Graeme such as Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)...Add one at time, refresh and then try. See section labelled Reviewing Windows NT Rights and Privileges Granted for SQL Server Service Accounts for the extra permissions.

Hope this helps.

Comments

[James B]

Sort-of....I had already seen this, is it really just "The account must be in the list of accounts that have "List Folder" permissions on the root drive where SQL Server is installed, and on the root of any other drive where SQL Server files are stored" So basically any domain user would just need to be a member of the local users group on the server it is running and have list folder permission on the drive root where sql server is installed? I thought there'd be more dark arts to this!

[James B]

this is a very thorough reply, thank you, I've already done what I needed to do, but if/when I have to do this again, I'll definitely use your answer as my starting point. Thanks!