Non-sysadmin needs ability to maintain SQL Agent Jobs

  • We have a team member that does not have sysadmin but we would like to give this person the ability to setup and maintain SQL Agent jobs.

    We've found that only a member of sysadmin can do this, unless of course this person owns the job. The jobs are all SSIS and all owned by the same SQL Account.

    We tried to have them work with the jobs by logging in as this SQL account but then access to the file system is restricted. We've tried all the MSDB roles but nothing seems to works.

    I'm just wondering if anyone knows what security settings would give someone the ability to modify Agent jobs? or if there is a work around?

  • SQL Show (4/5/2013)


    Have you tried Proxy Accounts ?

    http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/%5B/quote%5D

    thanks but did you read my post?

    We use proxy accounts to run jobs, what I'm looking for is a way to have someone create and maintain jobs without giving them sysadmin.

  • There is no simple way to do this without sysadmin I am afraid,, its a design flaw in SQL, but an intended one. There is a connect item about this, perhaps you would like to vote on it -

    https://connect.microsoft.com/SQLServer/feedback/details/156852/sql2005-sqlagent-msdb-security-roles#tabs/

    what I have done in the past is set up a SQL account to be used by a group of people and they had to use this account to maintain their jobs. the exact rights you would need to give it would depend on what the jobs did.

    dbcreator role possibly

    EXECUTE on master.dbo.xp_sqlagent_enum_jobs

    add the user to msdb

    add to role SQLAgentOperatorRole

    grant the account rights to use the proxy (execute sp_grant_login_to_proxy)

    grant select on msdb.dbo.sysproxies

    grant SELECT on msdb.dbo.sysjobs

    grant select on msdb.dbo.sysjobs_view

    grant select on msdb.dbo.syscategories

    this was in a dev environment rather than production

    ---------------------------------------------------------------------

  • This works for us:

    1. User must exist in the msdb database.

    2. User requires one of 3 roles in msdb database: SQLAgentOperator, SQLAgentReader, SQLAgentUser role.

    3. Make sure the principals are mapped to the proxy.

    The one "gotcha": Never let a non-admin explicity name a job owner as only admins have the permission to do that.

    If you are interested, we wrap the actions in a stored procedure that does the following:

    1. Dynamically creates a new job name

    2. Executes msdb..sp_add_job

    3. Executes msdb..sp_add_jobstep (make sure you specify the @proxy_name parameter!)

    4. Executes msdb..sp_add_jobserver

    5. Executes msdb..sp_add_jobschedule

    I may be missing something, but this should put you on the right track. See books online for privileges required to run the stored procedures starting with: msdn.microsoft.com/en-us/library/ms182079.aspx (sp_add_job).

    Steve Neumersky

  • Thanks for the feedback we'll take a look these ideas - Tom

  • No problem.

    In our solution we create jobs on the fly and set them to run one second after scheduling the job. By doing this you can still allow the SQL Server Agent service account to execute the jobs instead of getting cute with security assignments 🙂

  • sneumersky's solution using MSDB Roles works partially, because it does allow users to be able to run, edit or create Agent jobs without being a sysadmin, but it has limitations.

    1) The groups allow the users the described rights to ALL Agent jobs. I'd prefer to be able to give users the rights to manipulate some jobs but not others. For example, they should be able to run the backup job on certain databases whenever they want before directly editing the data in them, but I'd prefer they not be able to run Agent jobs on databases they don't deal with.

    2) This can be done in some instances by making the user the Owner of the job, but in some cases we have more than one user we'd like to give those rights to. I thought I could do this with an AD group, but you can't make an AD Group an object owner, nor can you give an agent job two owners.

    So what we have now is a situation where the users can access the Agent jobs they need, but we have to trust them not to mess with other jobs on the same instance.

  • Unfortunately, this is about as far as I could tip-toe the fine line between security and flexibility......unless this challenge could be solved using a custom "security table" and driving the solution that way. I have never tried that before.

    I would follow George's guidance on connecting with MS regarding this issue and seeing if they could provide a fix or a workaround 🙂

    Sorry I could not be of more help 🙁

  • Tom Van Harpen (4/4/2013)


    We have a team member that does not have sysadmin but we would like to give this person the ability to setup and maintain SQL Agent jobs.

    We've found that only a member of sysadmin can do this, unless of course this person owns the job. The jobs are all SSIS and all owned by the same SQL Account.

    We tried to have them work with the jobs by logging in as this SQL account but then access to the file system is restricted. We've tried all the MSDB roles but nothing seems to works.

    I'm just wondering if anyone knows what security settings would give someone the ability to modify Agent jobs? or if there is a work around?

    Seeing as all agent jobs are maintained in the msdb database, it makes sense to create a user with access to the database. This link http://msdn.microsoft.com/en-us/library/ms187901.aspx explains how.

  • Thanks for all the suggestions and tips this has been very helpful if only to verify that we have already tried it or to give us some new direction.

  • What is the final verdict? How did you eventually meet the customer requirement? Custom security table.....security trigger.....etc?

  • We dont have a good solution but we do have a work around.

    Basically right now we have a user that sets up jobs as themselves and once the job is setup correctly they change the owner to our standard ssis job owner acct. But, once that is done they are no longer able to make changes. So this person then uses the credentials of the ssis job owner acct to login and make changes.

    Based on the posts I've seen Microsoft has not provided a clean way for non-sysadmins to manage jobs.

  • 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/[/url]

  • Tom Van Harpen (6/25/2013)


    We dont have a good solution but we do have a work around.

    Basically right now we have a user that sets up jobs as themselves and once the job is setup correctly they change the owner to our standard ssis job owner acct. But, once that is done they are no longer able to make changes. So this person then uses the credentials of the ssis job owner acct to login and make changes.

    ....

    This is interesting. Perhaps create a stored procedure which will make this user the owner of this specific job. Grant execute to the new procedure for this user. Now the user can manage the job as needed without having access to the SSIS credentials. Additionally, you could schedule a job that will override the new stored procedure and revert the ownership to the SSIS account.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply