Is ‘sysadmin’ role required for the SQL Server and SQL Server Agent Service Accounts?

Need to know if ‘sysadmin’ role is required for the startup account for SQL Server Service and the SQL Server Agent Service; And if so, what is the reason behind the requirement etc. what will not be working properly if ‘sysadmin’ is not granted to startup account? 

When SQL Server is installed on Windows Server 2008 or later, SQL Server uses a Virtual Account (Managed local account) that is in the form of “NT Service\MSSQL$<InstanceName>”. These accounts are automatically provisioned as login and as sysadmin in SQL Server. If these accounts are removed from sysadmin role, issues documented in KB https://support.microsoft.com/en-us/kb/2620201/  can occur. 

If one uses the Local System account then it will have unrestricted access to all local system resources and since it’s a member of the Windows Administrators group, it is a member of the SQL Server sysadmin fixed server role.

https://msdn.microsoft.com/en-us/library/ms191543.aspx

If one uses a Windows Domain Account, then this account must have the following permissions: 

§  SeServiceLogonRight

§  SeChangeNotifyPrivilege

§  SeAssignPrimaryTokenPrivilege

§  SeIncreaseQuotaPrivilege

§  SeBatchLogonRight

 

SQL Server Agent must be a member of: 

§  sysadmin fixed server role.

§  To use multiserver job processing, the account must be a member of msdb database role TargetServersRole on the master server.

http://www.mssqltips.com/sqlservertip/1730/different-ways-to-execute-a-sql-agent-job/

The msdb stored procedure ‘sp_start_job’ is used by the SQL Server Agent.  By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:  

·        SQLAgentUserRole

·        SQLAgentReaderRole

·        SQLAgentOperatorRole

 Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own.

Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.

Members of sysadmin can start all local and multiserver jobs.

https://technet.microsoft.com/en-us/library/ms188283.aspx

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s