0

I'm trying to find or devise a way to allow a team of developers to be able to amend SQL Agent Job steps and schedules, but that doesn't involve Sysadmin rights for the devs. As I can't assign ownership of Agent jobs to a Group I need some sort of workaround.

So far I've come up with the following ideas

  1. Create a Login to the server and a matching User in MSDB and Master. Use this account to own Agent Jobs. To allow amendments, GRANT EXECUTE ON [msdb].[dbo].sp_update_job, sp_update_jobstep and sp_update_jobschedule to a group containing the developers.

This allows them to submit amendments to any Agent Job by using the MSDB stored procedures

  1. Create a Login / User as above, and make it owner of all the Agent Jobs. Then share the password with the developers.

Neither of these two scenarios are particularly secure for different reasons, but I'm trying to balance the needs of the security team versus the developers ability to perform their jobs - so a little pragmatism needs to be involved I think.

Are there any better ways to achieve this?

Hemanth Kumar
  • 314
  • 2
  • 7
Potatan
  • 13
  • 8
  • `the needs of the security team`...`Neither of these two scenarios are particularly secure for different reason` So if the security team doesn't want sysadmin, why would #2 not be desirable or an alternative? And why would sysadmin even be discussed as an alternative if that were the case? And if #2 is not secure enough, what would be an example of a process/scenario that is secure? – Greg Askew Sep 27 '22 at 15:14
  • Option 2 allows changes to be made with no audit trail, as there is no easy way of determining who was using the shared account. Also, sharing credentials is usually frowned upon for solid reasons. Sysadmin is only being discussed becuase that is a common workaround for problems such as this. But again it's not ideal. A bit like giving Windows users Domain Admin rights so they can access a network share – Potatan Sep 27 '22 at 15:25
  • `Option 2 allows changes to be made with no audit trail, as there is no easy way of determining who was using the shared account`. Then don't share accounts, have a separate account for each person. That doesn't seem very challenging. Most organizations have to provision multiple accounts in multiple systems anyway... – Greg Askew Sep 27 '22 at 17:36
  • SQL can only allow one user to edit an agent job, but I want multiple people to be able to edit any job. – Potatan Sep 27 '22 at 18:23

0 Answers0