Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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
share|improve this answer
add comment

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.

share|improve this answer
    
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! –  James B Oct 26 '10 at 10:16
add comment

Does this help?

share|improve this answer
    
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 Jan 11 '10 at 11:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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