asked
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
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.
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 David
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?
15 People are following this question.
