|
|
Monday, February 11th, 2013
|
12:09p - Creating a "SQL Job Launch Shell" for lower-priveleged users
This is in response to my question on 2/4/2013 for SQL Version 2000 (should work in subsequent versions if you follow my comments)
Design:
User Table Created w/ Trigger
( the tSQL code...Collapse )
Procedures:- check_job_queue - fires off via scheduled SQL job. It reads from the prod_support_job_queue table
- make_job_request - procedure exposed to the production support team. This helps them insert records into the prod_support_job_queue table
- sp_isJobRunning - (Modified this procedure from THIS publicly available code in order for it to run on SQL 2000 )
Logic:- The user makes his request via the make_job_request stored procedure. He is required to enter a valid job name, action (which is either START, STOP, or CANCEL)
- check_job_queue runs every 10 minutes for check for new actions in the prod_support_job_queue table. It utilizes system stored procedures in msdb to start and stop jobs. For the CANCEL command, a simple update statement is issued to the processed field to exclude it from further processing checks.
- sp_IsJobRunning is called by check_job_queue in order to see if the requested job is already running before issuing any commands
I am adding fine-tuning to the check_job_queue procedure. Once that is done, I'll post the code for the two custom procedures check_job_queue and make_job_request
(comment on this)
|
|
|
|
