Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|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

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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