permission to grant to a windows group to edit sql agent jobs created by another user


Can someone please advise me how i can grant a windows group  to be able to edit
 sql agent jobs created by a certain user without providing it syadmin permissions.

SP_2018 .IT ConsultantAsked:
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
I don't think there is any way to do that, not *edit* other users' jobs.  Only sysadmin can *edit* jobs owner by other users.

The SQLAgentReaderRole (in msdb) can be used to let them *see* other users' jobs.
Scott PletcherSenior DBACommented:
In theory you could set up a stored procedure that called the sp_change_job, and use EXECUTE AS to make it run under a sysadmin user, then just GRANT them execute on just that stored proc, but I've never had any luck getting all that to actually work (or work well).
This can be achieved using the SQLAgentOperatorRole in MSDB.  Refer to the following articles:

Explanations of SQLAgent*Roles

Configure a user to create and manage SQL Server Agent Jobs
Scott PletcherSenior DBACommented:
>> This can be achieved using the SQLAgentOperatorRole in MSDB. <<

Don't think so.  Not to *edit* another users' job(s).
Scott is correct.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.