asked
Is there a way to set a user's security to allow editing of SQL Agent jobs they do not own without granting them sysadmin rights
I need to give a user the ability to edit / change any SQL Agent job, regardless of ownership. Is there a way to do this in SQL Server 2008R2 without giving the user the sysadmin Server Role? Any help would be appreciated.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Yes. Use the fixed msdb database roles :
http://technet.microsoft.com/en-us/library/ms188283(v=sql.105).aspx It sounds like the role that you would want to use is **SQLAgentOperatorRole** . Review the permissions in BOL and decide from there. If you need more permissions than are offered, then you are looking back at sysadmin again unfortunately.
That's what I was afraid of. He is already assigned to all 3 of the SQLAgent roles on the msdb database. I'm afraid that I am going to have to evaluate other options. Thanks!
If these non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. :)
16 People are following this question.
