question

kasharp1229 avatar image
kasharp1229 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.
securityjobsuser
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
0 Likes 0 ·
ssharma14 avatar image
ssharma14 answered
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. :)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.