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