Job Execution System

In a production environment, as a DBA protecting the environment, you do not want to give out Admin rights to many, if any individuals in the company. You hold that right to be sacred, and selfishly keep it to yourself. This is a bit of job security, as well as protection for you and the system. What if multiple non-trained individuals were to access the database system, and make changes? Who typically gets the call to fix the system? The DBA, of course.

To the person that is denied this opportunity of power, they may see it as a slight on their education, intelligence, or even their person. After all, they have been around the system often longer than the DBA has. They have been involved for many years, and have many work arounds and even some favorite sql scripts that they have stored off for when they need to do certain tasks. As control of their system has shifted from their influence, to a process and policy driven environment, where division of duties exists, they become frustrated. "I used to be able to do X or Y", they exclaim.

As the DBA receiving these attitudes and complaints, I often feel empathy for them. I want them to be empowered. But I also want them to be controlled and monitored. I want them to be able to perform certain job functions, in a controlled fashion where their level of influence is limited, and their actions are logged. There is nothing like being told that the data in a table is all gone, and I have to restore it... only to find out that the individual reporting this discrepancy was the one that did the delete from table statement, and forgot to include the where clause.

So, with all that said, let's talk specifically about jobs. This individual that I have described above has been with the company for many years, and risen up the ranks, and is used to having a backdoor to the system, suddenly finds themselves unable to run a simple SQL job. They receive the phone call from the client begging for process X to be executed, yet they are helpless in this new process and policy driven environment to satisfy the request immediately, under their own power. They must create a ticket in the change control system; and make a request for job X to be executed. This takes time to enter into the system. More time for the ticket to go thru the system to be approved and eventually reach the intended party. More time for the intended party (DBA) to perform the action. As far as I, the DBA, is concerned, I just barely heard about the requested job needing to be ran, and I did it as soon as I could. But I am unaware of the time that has passed since the client requested it.

Let's be honest, running a job is not the most exciting task you may perform in a day as a DBA. Sometimes its monotonous enough you want to scream. So, how can I grant this access back to the individual that used to have this power? How can I allow this person to run a job, and do so in a controlled and monitored manner? How can they do their job effectively, but not have too much power?

We all know that we cannot make them admins, for with that level of access, way too much power is granted. So, the way I accomplished this is to create a Job Executions System; comprised of a table, a stored proc, a job, and a couple write rights granted.

System
I created a table called [_JobSystem] in the master db. This table has the following columns :
[JobName] [sysname] NOT NULL,
[Enabled] [tinyint] NULL,
[Execute] [tinyint] NULL,
[User] [sysname] default SYSTEM_USER NOT NULL,
[Datetime] [Datetime] default GetDate() NOT NULL,

This table will be the reservoir for a requested job and the preferred action to be taken on this requested job. The user will insert a record with the [JobName] they want and whether they want to enable the requested job or execute it. The table has a trigger on that checks if the [JobName] they input exists in the system, and will raise an error to that effect if it does not. This provides automatic feedback to the user that the requested job doesn't exist. If it does exist, then the record is added. The [User] field will be the SYSTEM_USER which is filled in with a default constraint. The [Datetime] field is also automatically filled in with the current DateTime. This record will sit in this table, until the Job Execution System job fires off, finds the record, and executes the request. Keep in mind that the job executed by this Job Execution System runs under the SQL Agent service account privileges, and will be able to execute other jobs. But the user has only been given access to write into a single table. Nothing more.

When the record is successfully inserted, a trigger fires off and a historical record is generated into another table [_JobSystemHistory]. This table has the following fields:
[ID] [int] Identity(1,1) NOT NULL,
[Datetime] [Datetime] default GetDate() NOT NULL,
[JobName] [sysname] NOT NULL,
[Enabled] [tinyint] NULL,
[Execute] [tinyint] NULL,
[User] [sysname] NULL,
[DatetimeCompleted] [Datetime] NULL,

When the user requested job is processed, this record is updated with a [DatetimeCompleted] field. This new value, along with the others previously inserted becomes our historical record of all jobs that have been processed, who processed them, and what processing occurred. The Job Execution System job that fires off on an interval of your choosing, and polls for records, calls a stored proc called [spProcessJobSystem]. This proc simply steps thru the [_JobSystem] table, and processes the records it finds. If we are too execute the requested job, we dynamically call sp_start_job. If we are to enable the requested job, we dynamically call sp_update_job. We then compose an email to be sent to the user that requested the action. This lets them know what happened, when, etc. They receive this on a delay from their inserting into the table, but then will eventually get this notification that informs them of actions taken.

The Job Execution System job we just described can be scheduled to execute as often as you wish. This produces a delay in the request the user inputs. They must be made aware that the job will not fire off, except at a certain interval. A couple minutes is sufficient for our shop, and seems to keep the users satisfied.

Modifications and improvements to this system can obviously be made and will not be described here. You can add to this idea as much as you see fit. Most the individuals I have dealt with, and given access to this system are familiar enough with SQL syntax, that they were more than happy to insert records into a table. Obviously you could create a front end application that displayed the possible jobs, and did the work that way. I chose the less obvious route, and added a level of security, by forcing them to know the job name they wished to process. Most people will not know the job names.

There you have it. Users that no longer have the cowboy abilities they once used, can now fire off a job, with historical tracking of their actions and security to prevent them from performing operations they should not. They are empowered to run jobs at their whim, and you are left to do what DBA's do best.