SQL Agent Job Manager

Question

SQL Agent Job Manager

AndrewL65

Mr or Mrs. 500

Points: 526
More actions
December 1, 2016 at 8:55 am

#401146

I have a team of developers who have traditionally managed their own agent jobs. I'm now trying to remove their sysadm access to the live servers but this will also remove their ability to manage jobs now owned by them.
Has anyone come across a way to do this? Giving them SQLAgentOperator role only allows them to alter jobs owned by themselves (useless for a group of people).
I think Idera have a program to do this but then again it required sysadm access (also the Enterprise version is a bit pricey)...

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

AndrewL65 (12/1/2016)
I have a team of developers who have traditionally managed their own agent jobs. I'm now trying to remove their sysadm access to the live servers but this will also remove their ability to manage jobs now owned by them.
Has anyone come across a way to do this? Giving them SQLAgentOperator role only allows them to alter jobs owned by themselves (useless for a group of people).
I think Idera have a program to do this but then again it required sysadm access (also the Enterprise version is a bit pricey)...

You might want to take a look at using proxy accounts for this - that's what we've done in the past.

Steps to do this are in the MS documentation:

https://msdn.microsoft.com/en-us/library/ms187901.aspx

Sue

AndrewL65 Mr or Mrs. 500 Points: 526 More actions · Answer 2

Hi Sue,

I've tried this in the past and couldn't get it to work. I didn't think that creating a proxy would enable non sysadm users to modify any job.

Regards,

Andrew

Sue_H SSC Guru Points: 90860 More actions · Answer 3

AndrewL65 (12/6/2016)
Hi Sue,
I've tried this in the past and couldn't get it to work. I didn't think that creating a proxy would enable non sysadm users to modify any job.
Regards,
Andrew

If you need them to manage and edit any and all jobs then the only option is if they are sysadmins. There are other workarounds for various scenarios - using the roles, certificates, proxy accounts, etc. They could have a shared login which only have access to the jobs they own and limited privs with the agent roles but none of that will work since they need to edit all jobs.

Not sure why developers would be editing and managing backup jobs, index maintenance, etc.

Sue

AndrewL65 Mr or Mrs. 500 Points: 526 More actions · Answer 4

Sue_H - Tuesday, December 6, 2016 5:32 AM
If you need them to manage and edit any and all jobs then the only option is if they are sysadmins. There are other workarounds for various scenarios - using the roles, certificates, proxy accounts, etc. They could have a shared login which only have access to the jobs they own and limited privs with the agent roles but none of that will work since they need to edit all jobs. Not sure why developers would be editing and managing backup jobs, index maintenance, etc. Sue

In the end I decided to write my own job manager in MVC/C# It uses a SQL account that that has sysadm privileges to make the actual changes and also has additional security on which jobs that can maintain.

If anyone wants a copy, the Github repo is at GitHub. You might have to modify it a bit to get it to run in your environment as I've put a few checks in for my own (security groups etc)