SQL Server and SQL Server Agent service account(Startup Account) and Permissions

1.Does service account need sysadmin role?

 [SQL Server(Database Engine)]
 沒有文件說明一定需要sysadmin role
 no document found saying No or Yes explicitly about if sql service account need to be a member of sysdamin role
 (1)SQL 2005,service account is always granted with sysadmin by default (e.g. if we pick Local System as our service account, then we would have NT Authority/System having sysadmin in the SQL instance).
   How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005
   http://support.microsoft.com/kb/283811
   Base on KB283811 for SQL 2005, When changing new SQL service Account, sysadmin role is a must

 (2)SQL 2008,in Windows 2008 R2, Virtual Accounts(SQL Server Per-service SID)=NT SERVICE\<SERVICENAME>is always granted with sysadmin by default
   Configure Windows Service Accounts and Permissions
   http://msdn.microsoft.com/en-us/library/ms143504.aspx

 基於以上文件,建議將SQL Server service accoount(startup account)加入sysadmin role

 

 [SQL Server Agent]
 雖然SQL Server Agent service的啟動帳戶不是sysadmin role也能啟動SQL Server Agent,但可能會限制某些功能。

 (1)必須是sysadmin role
   From Books Online and Training kit 70-462
   The SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role, and if multiserver job processing is used, the account must also be a member of the msdb database role TargetServersRole on the master server.
   Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager)
   http://msdn.microsoft.com/en-us/library/ms186264.aspx

   From Training kit 70-462
 (2)The account you assign for the SQL Server Agent service during installation is added automatically to the sysadmin fixed server role during installation.
   PS.但是經測試SQL 2008 R2安裝過程時所指定一個local or domain user account也不會自動加入sysadmin role

 (3)If you modify the account used by the SQL Server Agent service at a later point, SQL Server Configuration Manager does not automatically add the account to sysadmin role.
   PS.透過SQL Server Configuration Manager修改啟動帳戶,只會給予需要的權限,不會自動加sysadmin role

 

 

2.Does service account need Windows Administrator Permission?(local Administrators Group member)

 [SQL Server(Database Engine)]
 (1)不需要一定是Administrators group member
 (2)視需求選擇其中一種account
    Domain User Account
    Local User Account
    Local Service Account(NT AUTHORITY\LOCAL SERVICE)
    Network Service Account(NT AUTHORITY\NETWORK SERVICE)
    Local System Account(NT AUTHORITY\SYSTEM)

    Windows 7 and Windows Server 2008 R2 have two new types of service accounts called managed service accounts (MSA) and virtual accounts.
    Virtual Accounts
    Managed Service Accounts
  
    Configure Windows Service Accounts and Permissions
    http://msdn.microsoft.com/en-us/library/ms143504.aspx

 

 [SQL Server Agent]
 (1)Beginning with SQL 2005, 不需要是Administrators Group member
    Beginning with SQL Server 2005, SQL Server Agent no longer requires that the service startup account be a member of the Microsoft Administrators group.
    However, there are some restrictions associated with using a non-administrative account for the SQL Server Agent service. For more information, see Service Account Types Supported for SQL Server Agent.
    http://msdn.microsoft.com/en-us/library/ms345380(v=sql.105).aspx


 (2)如果是domain account可能需要以下權限
    From Training kit 70-462
    When choosing to use a domain-based security account, ensure that the account has the following permissions:
    ■ The logon as a service right. You assign this right by using Group Policy.
    ■ Membership of the Pre-Windows 2000 Compatible Access security group at the domain level. If you do not add the domain-based security account used for the SQL Server Agent service to this group, any jobs owned by domain users who are not members of the local Administrators group on the host computer will fail.

 

PS.

1.Default provisioning login after SQL Server setup finish

2.Default provisioning windows groups