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

Hi,

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.

Thanks.
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.
0
 
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).
0
 
dave_tillerCommented:
This can be achieved using the SQLAgentOperatorRole in MSDB.  Refer to the following articles:

Explanations of SQLAgent*Roles
http://msdn.microsoft.com/en-us/library/ms188283.aspx

Configure a user to create and manage SQL Server Agent Jobs
http://msdn.microsoft.com/en-us/library/ms187901.aspx
0
 
Scott PletcherSenior DBACommented:
>> This can be achieved using the SQLAgentOperatorRole in MSDB. <<

Don't think so.  Not to *edit* another users' job(s).
0
 
dave_tillerCommented:
Scott is correct.
0
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.