SQL Agent permissions

Question

SQL Agent permissions

Henry_Lee

SSCrazy

Points: 2714
More actions
September 30, 2010 at 8:39 am

#224573

Good day,
In our environment every developer has a backup. I'm looking for a way to configure permissions on a job so the backup developer and primary developer have the same permissions.
The SQL Agent fixed roles don't really work well for this. The most privileged role only allows you to edit jobs you own - so the backup guy can't edit a job owned by the primary guy. Also, the most privileged role allows you to start / stop all jobs, which may not be ideal.
I don't want to create a shared domain account, because that lacks transparency and accountability. Adding folks to the sysadmin role, obviously a deal killer.
This scenario can't be all that unusual - how do you handle this?
Anyone have any creative solutions?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

You can create an AD group, add both users to the group, and then grant permissions to that. I'm not sure if this would work in your case, but worth a shot.

Henry_Lee SSCrazy Points: 2714 More actions · Answer 2

Thanks for the suggestion Derrick, but that won't work. A job can't be owned by a group, it has to be owned by an individual login.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Indeed, just tested that after I posted.

I really do not like how permissions for jobs/ssis/dts packages are handled..you either give not enough access, or sysadmin...there's almost no in-between that works.

Geoff A SSChampion Points: 11511 More actions · Answer 4

if you do not like the bult in Roles, create your own with the proper GRANTS;

USE [msdb]

GO

CREATE ROLE [TestRole1] AUTHORIZATION [dbo]

Go

--- Step 2 -------- Assign Permissions to role ------------

USE [msdb]

GO

GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE

ON SCHEMA ::dbo

TO [TestRole1]

Go

Henry_Lee SSCrazy Points: 2714 More actions · Answer 5

Thanks Geoff for the suggestion, but I'm not sure how that helps. The jobs are not objects that you can grant permissions to.

I may not understand what you are suggesting...

Geoff A SSChampion Points: 11511 More actions · Answer 6

it was more of a comment to the person that said they did not like the fixed roles SQL offered.

If you want more fine tuned control of roles to msdb, for SQL agent, make your own.

you could do things like deny on the sp_add_job or grant on sp_delete_job

in your example, grant sp_update_job on <my newly created role>

depends on what you are trying to accomplish.