Stop handing out sysadmin for job access: SQLAgentUserRole, ReaderRole and OperatorRole compared

Developers want to see their jobs. Support staff need to rerun a failed load at 6 am. Neither needs sysadmin. msdb has three fixed roles for exactly this. Each role includes the permissions of the one before it.

#What each role can do

Notes:

• Jobs created by these users are owned by them and run as them: T-SQL steps under their login, and other step types only through proxies granted to them.
• Members can't change a job's owner to someone else. Only sysadmins can.
• Multi-server (MSX) jobs are view-only for these roles.

#Grant to groups, not people

USE msdb;
CREATE USER [CORP\SQL-Finance-Support] FROM LOGIN [CORP\SQL-Finance-Support];   -- the login must exist at server level
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [CORP\SQL-Finance-Support];

CREATE USER [CORP\SQL-Developers] FROM LOGIN [CORP\SQL-Developers];
ALTER ROLE SQLAgentReaderRole ADD MEMBER [CORP\SQL-Developers];

#Typical mapping

Warning

SQLAgentOperatorRole can start every local job, including backups, index rebuilds and other teams' jobs. If a group needs to run only a few jobs, use a certificate-signed wrapper procedure instead (see the post on ways to run a job).

#Review who has Agent access

USE msdb;
SELECT r.name AS agent_role, m.name AS member, m.type_desc
FROM sys.database_role_members rm
JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals m ON m.principal_id = rm.member_principal_id
WHERE r.name LIKE N'SQLAgent%Role'
ORDER BY r.name, m.name;

-- jobs owned by non-sysadmin users (created through these roles)
SELECT j.name, SUSER_SNAME(j.owner_sid) AS owner
FROM msdb.dbo.sysjobs j
WHERE IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME(j.owner_sid)) = 0;

# estate-wide with dbatools
Get-DbaDbRoleMember -SqlInstance (Get-Content servers.txt) -Database msdb -Role SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole |
    Select-Object SqlInstance, Role, UserName | Sort-Object SqlInstance, Role

#Common questions

A user in SQLAgentReaderRole can't see the Agent node in SSMS. They need a user in msdb with the role. The login alone isn't enough. Also check they connected to the right instance, and that Agent is running.

An operator can't start a job: "The specified @job_name ... does not exist". The job may be a multi-server job, or the user isn't in SQLAgentOperatorRole on this instance (AG replicas each have their own msdb).

A developer's job fails with a permissions error. Their job runs as them, not as the Agent account. Grant their login the needed rights in non-prod. In prod, move the job to a service owner.

Can I make a custom role? You can, by granting EXECUTE on specific msdb procedures. But the Agent procedures check fixed-role membership internally, so custom roles behave unpredictably. Use the fixed roles, or certificate-signed wrappers.

Tip

Put Agent role membership in your access review alongside sysadmin and db_owner. SQLAgentOperatorRole is effectively the power to run anything on the server at 3 am.