Grant an AD Group permission to edit all MSSQL Agent jobs

Question

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication.
Our AD accounts are added to an AD group, which is used to grant all permissions on MSSQL. For simplicity, we don't grant any permissions directly to our accounts, and for security we don't use local MSSQL accounts.
We're struggling to setup permission so that everybody (on the AD group) is able to list, view, edit, start, stop, enable, disable, etc MSSQL Agent jobs, and list and set proxies on CmdExec steps. It shouldn't matter who had created each job, we should all have full access to all jobs.
I had googled about it, read answers here, made a question myself. I read the SQL Server Agent Fixed Database Roles doc and noticed that SQLAgentOperatorRole is the most privileged role and still doesn't have full access to all jobs.
Is there any way to have this kind of permission set for an AD group?
If not, my next path will be to create a local MSSQL account, deny it permission to R/W on all databases, and then everybody will know its password and all jobs be created and managed from it. But I really wish to not do such thing.

David Browne - Microsoft · Answer

As @MSSQLServerDBA says only a sysadmin can alter another user's job or change the job's owner.  But the reason for this is that any user who can do that is effectively a sysadmin.
SQL Agent TSQL Job Steps always connect as the Agent Service which is always a sysadmin, but before the job step Agent impersonates the job owner with
EXECUTE AS LOGIN = N'JobOwner' WITH NO REVERT

So if a user can alter a job owned by a sysadmin, or reassign a job to a sysadmin, that user can run arbitrary commands as a sysadmin.

MSSQLServerDBA · Answer

Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.

Answered by MSSQLServerDBA on December 31, 2021

Grant an AD Group permission to edit all MSSQL Agent jobs

2 Answers

Add your own answers!

Ask a Question