Creating a "SQL Job Launch Shell" for lower-priveleged users

    Date: 02/11/13 (SQL Server)    Keywords: sql

    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)

    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')

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

       if (
          declare @username varchar(30)
          declare @log_date datetime
          declare @job_name sysname
          -- Get the user's attributes.
            @username = loginame
         where spid = @@spid

        select @log_date = getdate()
        select @job_name = job_name from inserted
        update prod_support_job_queue
        set log_date=@log_date,
             processed ='N'


    • 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 )
    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


« Data warehouse community || Stripping time out of... »

antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home