4

I'd like to give a user the ability to administer SQL Agent jobs. According to the doc page, I can do this by granting the "appropriate" roles on msdb. I've granted all three SQLAgent% roles, but the user gets this error when (as a test) trying to rename a job:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
(Microsoft SQL Server, Error: 14525)

This question is similar, but no answer there addresses SQL Agent. Is the sysadmin role the only solution, in MS SQL 2K8?

Improve this question
5

Unfortunately, administration of Agent jobs can currently only be done by members of the sysadmin role. The relevant documentation is found on MSDN. You'll note that the three Agent roles (User, Reader, and Operator) have different levels, but ultimately a user without sysadmin can only edit/manage jobs that user owns and not jobs created by other users.

Improve this answer
  • Sad panda. Your link is the 2012 version of the page I linked earlier; neither, however, seems to say anything about only sysadmin users being able to edit others' jobs. Unless I missed it? – Jon of All Trades Apr 10 '13 at 18:05
  • You can look at the older versions from the "Other versions" drop down, but there isn't much that has changed. It doesn't explicitly say only needing sysadmin, but all the documentation shows that the Agent roles you can grant only allow editing of jobs owned by the login. It's declared by its absence. – Mike Fal Apr 10 '13 at 18:21
3

It can by done by overriding of sp_update_job procedure. Steps described here: http://borishristov.com/blog/modifying-not-owned-sql-agent-jobs-without-being-a-sysadmin/

Improve this answer
-2

Page https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles says "A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent." (emphasis mine)

So you SHOULD not need to be a member of the sysadmin role to see SQL Server Agent, which contradicts what Mike Fal said above. Maybe it's a SQL Server or SSMS bug...

Improve this answer
  • No, Mike Fal said that users with the other roles, but without sysadmin, cannot change or delete jobs they don't own. He didn't say they couldn't "see SQL Server Agent", and that's not what this question was about. – RDFozz Dec 8 '17 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.