HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does the Sql Server Agent account need sysadmin permission?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
theneedsqlpermissionagentaccountsysadmindoesserver

Problem

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent wouldn't start. (See image below). There's nothing of note in the event log. When I restore the sysadmin permission the agent starts with no difficulty.

My account is a managed service account. None of the other managed service accounts I use (one for running reporting services, and one for running the SQL service) have sysadmin permission.

Is the sysadmin permission actually necessary? If not, what are the minimum permissions this account needs?

Solution

Per BOL: Configuring Windows Service Accounts and Permissions

SQL Server Agent Login and Privileges
The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.

Note that as of SQL Server 2016, membership in the sysadmin SQL Server Role is no longer required, even though by default the service account is granted that membership during setup. Also, the service account does not need to be a member of either the local server's Administrator group, nor does it need any special group membership at the Domain level such as membership in the Domain Administrators group. Adding the account to those groups could be a serious security issue since a SQL Server Agent job could be used to perform actions against the local machine or the domain. Membership in the local servers Administrator group is required if you want SQL Server Agent to be able to restart SQL Server on failure.

If you allow non-sysadmin users to create SQL Server Agent jobs, the service account must be allowed to impersonate those users, using SQL Server Impersonation. Granting impersonate permission consists of:

GRANT IMPERSONATE ON USER::[myuser] TO [NT SERVICE\SQLSERVERAGENT];


Where [myuser] is the database principal to be impersonated, and [NT SERVICE\SQLSERVERAGENT] is the name of the server principal used by SQL Server Agent.

The permissions assigned to the SQL Server Agent virtual account (a login named similarly to NT SERVICE\SQLServerAgent) during SQL Server setup are:

  • Membership in the sysadmin SQL Server role.



  • Log on as a service (SeServiceLogonRight)



  • Replace a process-level token (SeAssignPrimaryTokenPrivilege)



  • Bypass traverse checking (SeChangeNotifyPrivilege)



  • Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)



When making changes to the service account used to run either SQL Server or SQL Server Agent, not the following:

Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but Don't change all the required settings.

SQL Server Agent also requires permissions to the msdb database, although those permissions are not directly documented. It is likely that granting the SQL Server Agent login membership in the msdb database db_owner role provides the required permissions.

Code Snippets

GRANT IMPERSONATE ON USER::[myuser] TO [NT SERVICE\SQLSERVERAGENT];

Context

StackExchange Database Administrators Q#59128, answer score: 7

Revisions (0)

No revisions yet.