User with permissions to modify a job?

Hello everyone,

I'm David, 24 year old and based in germany. Im a pretty new 'junior-dba' who is in the middle of his learning process and this is my first question to the 'senior-dbas' out there.

We have a user who has to modify jobs (the commands inside a jobstep)on our PRD DB without god-access (sysadmin).

At the moment the user is in the 'SQLAgentOperatorRole' and is able to enable/disable, set a new shedule and more stuff like that. But he isnt able to modify the jobsteps.

Is there any chance to give him the mighty force to modify the commands in a step?

We also tried several things:

  1. make him as the owner and try to run the job with sysadmin credentials

  2. use a proxy, but its a tsql script

  3. Grant the role SQLAgentOperatorRole execution rights to 'sp_update_jobstep'

  4. Make him as db_owner for msdb

more ▼

asked Sep 12, 2013 at 11:04 AM in Default

avatar image

150 4 6 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The SQLAgentOperatorRole is supposed to have permissions to create/modify/delete jobs they own. If you have not seen or read this here is a link to the TechNet information. http://technet.microsoft.com/en-us/library/ms188283.aspx. Here is another one on Configuring Users for Agent Jobs. http://technet.microsoft.com/en-us/library/ms187901.aspx

You may want to make sure the permissions are set correctly.

On another note: I would consider only granting these permissions in an environment other than production. Say development. The idea being only a certain number of people, DBA, have access to work on production. (Less cooks in the kitchen makes a better soup).

If that is not possible, hopefully you have a change tracking system setup so all changes are documented and approved before changes are made in production.

more ▼

answered Sep 12, 2013 at 12:21 PM

avatar image

sqlaj 1
5.5k 4 6

Hey, thanks for your answer :)

The job owner cant be changed to SQLAgentOperatorRole, so that isnt really an option for us cause the job has to run among sysadmin credentials.

We know that there is a risk to grant user the permission to modify a job on PRD, but the user was before in the sysadmin role, and in case of our "Clearence" we decided to drop him out of the god-access.

Is there any chance to give him the permissions to modify several jobs or atleast all jobs?

The user was before the owner of the jobs we know changed to 'sa', but he still needs access to it :)

thanks in advance, cheers


Sep 13, 2013 at 07:31 AM david.hoffmann

David, Yes using the 'sa' as owner is the best option. Especially in the case where you mention, the person was dropped from admin role or worse where they actually leave the company. As far as I know, the highest level of access for jobs is what you have granted, besides sysadmin. We don't allow that where I work so I have limited experience working the way you are trying to do. We grant higher permissions on lower systems like development. They get everything working, test in "test" or pre-prod then we add/update in prod. Maybe others can provide more information? Perhaps revisit the proxy? Make sure that was setup correctly?

Sep 13, 2013 at 01:57 PM sqlaj 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 12, 2013 at 11:04 AM

Seen: 626 times

Last Updated: Sep 13, 2013 at 03:06 PM

Copyright 2018 Redgate Software. Privacy Policy