8

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.

enter image description here

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?

1
  • 2
    For those not familiar with sp_Blitz, the check the OP is talking about is brentozar.com/blitz/security-sysadmins. It checks for members of the sysadmin fixed server role as a notification. There is nothing necessarily wrong with people having sysadmin access but knowing who can drop a database is good information and possibly something to look into why they have that access. Feb 18, 2014 at 20:58

2 Answers 2

7

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:

  1. Membership in the sysadmin SQL Server role.
  2. Log on as a service (SeServiceLogonRight)
  3. Replace a process-level token (SeAssignPrimaryTokenPrivilege)
  4. Bypass traverse checking (SeChangeNotifyPrivilege)
  5. 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.

0
7

The Microsoft Learn article, Select an Account for the SQL Server Agent Service, also says:

The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:

  • The account must be a member of the sysadmin fixed server role.

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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