Hello,

Looking for best practices here.  Our SQL jobs in production are owned by application IDs, not individual users.  What I am reading is you must be a sysadmin to change a job, or be able to login as that application ID.  What do other IT shops do?  Give the application password to the developers in production?  Or do other IT shops really give their developers sysadmin in production environments?  Or do you require all job changes to go through DBAs in production?

We have 2 solutions - set up an application account to own the jobs, where the development team all have the password.  This has an issue for me because it lacks accountability - you don't know who logged in and changed what.

Solution 2 was to create an AD security group with the developers included.  This provided accountability since they would log in with their own id, but required they be granted sysadmin to change the jobs!  This model also threw errors even for creating jobs, aside from the sysadmin grant, which would not be acceptable in production.

The 3rd solution is for all job changes to go through the DBAs.  The development teams are baulking at this, but is this the "right" way?  What is the best practice for job modification in production?

Input appreciated,

Dianne

I think you are right-- even with the sql agent roles, you can change the jobs that you own but not the other jobs.

and I believe, the commonly used one is option 3. Have dba do the job scheduling. also, typically, job owner would be SA. that way you do not have to deal with invalid logins anymore.

One of things, I had to do as a workaround is to make stored procedure code where the developers can change the schedule of the job, if  they need to.

the reason for this is: sometimes, the input files, would not come on time,so, had to reschedule the job. I did not want to be bothered at night to run the job, so, I made a stored procedure - that would let him/her update or disable the current schedule and create a new job schedule. This is will also, email that who has changed and all that...

this worked fine for us. 

I would personally prefer the third option, DBA's to maintain jobs. Its not suggested to share prod credentials/acess to developers. Instead you can ask your developers to create executable batch files which in turn exececutes sql commands to manage jobs.