locked
Unable to Edit SQL Agent Jobs owned by non-sysadmins RRS feed

  • Question

  • I have a situation where a group of developers (non-sysadmins) who have db_ssisadmin and SQLAgentUserRole, SQLOperatorRole,SQLReaderRole. They upload the packages to msdb from BIDS and the SQL Agent jobs is scheduled by each developer using proxy account. But they are unable to edit the SQL Agent job owned by another developer. They want to be able to edit jobs owned by each other. Is there a way to achieve this.

     

    Thanks


    SSAS DBA
    Friday, November 11, 2011 9:10 PM

Answers

  • Editing a SQL Agent job is restricted to the owner.   A Windows Group cannot own a job.  It is the wrong type of account. The job owner must be either a SQL or a Windows Login. I have read (and guess that I basically understand) the reasons why this is necessary for changing security context.

    You see the same behavior that EXECUTE AS cannot be a group.  But it would be nice to have groups own things like jobs.  There is a Microsoft Connect item asking for this, but it has been closed.

    How can you work around this?   I have some jobs owned by service logins, e.g. 'DepartAJobOwner' and those who can login with that account can manage those jobs.

    So, I have to give the appropriate people the password so that they can login as this service login in order to edit the job.  Of course, all the jobs are owned by that account. (Or by those accounts, since all jobs do not need to have the same owner.)

    Hope that helps,

    RLF

    • Marked as answer by amber zhang Friday, November 18, 2011 1:58 AM
    Friday, November 11, 2011 9:49 PM