Understanding SQL Agent Jobs

Question

We are trying to define proper security for SQL Agent jobs.  This is very confusing.

1)  We do not have individuals own jobs.  We have sa or application IDs own jobs

2) DBAs do not support jobs written by other teams (developers or ETL admins)

3) Active Directory groups, even with database sysadmin and SQL Agent operator cannot modify jobs owned by sa or applid.  Neither can a domain individual user.

Is Microsoft really telling me that I have to grant sysadmin/serveradmin to developers in production in order that they can maintain their jobs?  Or scenario B, that we must create a group account with a shared password to own the jobs?  Or that a DBA must attend every job failure?

The first 2 options are unacceptable in today's security environment, and the 3rd is not practical.  Help!!!  What am I missing?  What is the best practice for modifying jobs in production?

Thanks kindly,

Dianne

---

Dianne

Answer 1

Hi Dianne,

ONLY a sysadmin can modify someone else's job. If developers need to modify their jobs then they either need to be the owner or sysadmin.

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

I know that's not an ideal answer but it's the only one to give. Even giving DBO on MSDB doesn't give the user the ability to manage other user's jobs. Generally, a DBA would be in charge of making changings in production due to this limitation.

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

Answer 2

So what sort of jobs are these? For a job that performs some maintenance operation in a database on application, all there has to be is a call to a stored procedure. The stored procedure may have to be changed every now and them - but not the job.

I don't know whether this also can be applied to SSIS packages, as SSIS is a white spot on my map. (And even if not the updated packages needs to be stored somewhere, and that is not in the application database.)

I can only agree that the model with a centralised job scheduler does not work well on a consolidated server with many databases.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

I believe the general idea is that developers never directly touch ANYTHING in production, except in extreme circumstances.  Their support for jobs is developed in dev environments, and validated through QA and staging processes.

I've never really seen an environment use the available SQL Server privileges and security to parcel out access successfully, it always breaks down to needing SA.

Of course with the current fad idea of "DevOps" ... well, that's entirely incompatible with decent security from the git-go, so good luck and happy trails!

Josh

Answer 4

I believe the general idea is that developers never directly touch ANYTHING in production

Josh

/agreed!  Seems like devs in production would be some sort of audit violation.

Answer 5

I agree as well - needed some support from the community to convince the developers this was proper security.  Thank you all who answered.

---

Dianne