0

I have a non-sysadmin user whose ID (Windows login) is set as owner on a set of SQL Agent jobs. The user also has the SQLAgentReader role in MSDB. The user can see the Agent node in SSMS, can view their jobs, see the job history for their job, manually start their jobs. However, they cannot EDIT their job steps to make updates when needed. They receive the error message 'EXECUTE permission denied on object sp_update_jobstep'. I even granted their ID explicit EXECUTE permission on the three job update stored procedures in MSDB: sp_update_job, sp_update_jobstep, sp_update_jobschedule, yet they still cannot make updates to the job step. They still receive the EXECUTE permission denied error even after the permission updates and restarting SSMS.

I do not want to make them a member of the SQLAgentOperator role, as it has too much access to other jobs, and will not consider granting them sysadmin role. Is there any other way to allow them to update steps in the jobs in which they are the job owner?

Improve this question
1
  • Rather than look for an alternative, you need to find out why this is happening. The documentation shows that users with the SQLAgentReader Role should have access to update their own jobs, including running sp_update_jobstep (msdb roles and sp_update__jobstep)
    – HandyD
    Commented Sep 5 at 6:37

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.