locked
Give non-sysadmin user access to SQL Agent Jobs RRS feed

  • Question

  • Hi, I have a situation where a group of users (domain ad group) need access to create and edit SQL Agent Jobs. The group can not be sys admin. I tried with the server role SQLAgentOperatorRole. but that doesn't give access to edit all SQL Agent Jobs, only the one that the users owns.

    Thanks in advance

    Monday, March 7, 2011 9:11 AM

Answers

  • haha well yeah that's always the way it starts...

    You've got several other options open to you such as...

    1. Create a job that scans through the jobs and standardises the ownership of any new ones.
    2. Create some utility procedures that they can use to take ownership and any other operations you might want using escalation of security i.e. using EXECUTE AS
    Good luck, because the next stage is them wanting even more access :)

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by WeiLin Qiao Tuesday, March 15, 2011 11:31 AM
    Monday, March 7, 2011 7:15 PM

All replies

  • This problem is a bit of a pain I have to agree, however you'll see in the Rights matrix that the behaviour being demonstrated is correct SQL Server Agent Fixed Database Roles

    Is this server a Development box? Reason I ask is that there are a few possible solutions to your problem, but the real dirty and easy way to do this is add the AD Group as an MSDB dbowner. You will obviously understand the implications of that but since you already want them to be able to have full rights to all the jobs you will already be a little exposed.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Monday, March 7, 2011 10:16 AM
  • Hallo Nik,

    u have to use the SQLAgentUserRole for these issues.

    More information about the roles and it's permissions here:
    http://msdn.microsoft.com/en-us/library/ms188283.aspx


    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Monday, March 7, 2011 10:21 AM
  • From that url that we both gave "SQLAgentUserRole have permissions on only local jobs and job schedules that they own" niklasrene is after change rights to ALL jobs not just the ones they own.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Monday, March 7, 2011 10:52 AM
  • Thats correct Mark, and thats a bit of a pain just like you mentioned.

    Yes, this is a development box, the users are business analytics, and I never thought they would need more than read access to this server.

    Niklas

    Monday, March 7, 2011 6:44 PM
  • haha well yeah that's always the way it starts...

    You've got several other options open to you such as...

    1. Create a job that scans through the jobs and standardises the ownership of any new ones.
    2. Create some utility procedures that they can use to take ownership and any other operations you might want using escalation of security i.e. using EXECUTE AS
    Good luck, because the next stage is them wanting even more access :)

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by WeiLin Qiao Tuesday, March 15, 2011 11:31 AM
    Monday, March 7, 2011 7:15 PM
  • This problem is a bit of a pain I have to agree, however you'll see in the Rights matrix that the behaviour being demonstrated is correct SQL Server Agent Fixed Database Roles

    Is this server a Development box? Reason I ask is that there are a few possible solutions to your problem, but the real dirty and easy way to do this is add the AD Group as an MSDB dbowner. You will obviously understand the implications of that but since you already want them to be able to have full rights to all the jobs you will already be a little exposed.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you

    I set my local user to dbowner on MSDB but still cannot modify agent jobs owned by others.  SS2008 R2.  Was there something else I needed to do?  Thanks.
    Friday, April 1, 2011 4:56 PM
  • Hey...You can give msdb database access with some other role

    (ie: SQLAgentOperatorRole;"SQLAgentUserRole......)

    Monday, July 18, 2011 8:06 AM