Creating a "SQL Job Launch Shell" for lower-priveleged users (original) (raw)

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
CREATE TABLE [dbo].[prod_support_job_queue] (
[job_name] sysname NOT NULL,
[step_id] int NOT NULL CONSTRAINT [DF__prod_supp__step___4959E263] DEFAULT (1),
[action] nvarchar(6) NOT NULL, (Must be either START, CANCEL, or STOP)
[ntlogin] nvarchar(32) NULL, --used to log who made the request
[log_date] datetime NULL,
[processed] char(1) NOT NULL CONSTRAINT [DF_prod_support_job_queue_processed] DEFAULT ('N')
)
ON [PRIMARY]

CREATE TRIGGER [dbo].[ti_job_queue] on [dbo].[prod_support_job_queue]
for insert
as
set nocount on

if (
update(job_name)
)
begin
declare @username varchar(30)
declare @log_date datetime
declare @job_name sysname

-- Get the user's attributes.
select
@username = loginame
from
master..sysprocesses
where spid = @@spid

select @log_date = getdate()
select @job_name = job_name from inserted

update prod_support_job_queue
set log_date=@log_date,
ntlogin=@username
where
processed ='N'
and
job_name=@job_name
end

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:

1. 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)
2. 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.
3. 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