Amy (amyboyd) wrote in sqlserver,
Amy
amyboyd
sqlserver

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
  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
Tags: msdb, tsql. jobs
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 0 comments