Thursday, May 19, 2011 #

SQL Agent Job Queue proxy

Ok, so you have locked your SQL down. No users allowed. But then there is this information worker for whom the IT department made a special SSIS package. And they loaded the package in to the SQL server and created a SQL Agent job for it. Well no problem so far. But now the user wants to be able to start the job when ever he needs to. Hmmm, huge problem. Because you need to have SA equivalent rights. What?? Yes, you read it right. SA equivalent !! Hell no! Ok a sql geek will now say, that's wrong, there is the operator role for that. That is true, BUT in my experience that role doesn't cut it for several reasons.

So here is the solution. The SQL script here will create a database, a couple a tables and a stored proc. It also will create a sql agent job that will loop. Why do you ask. Well the idea is to have a low level security object which a user can use to request job executions. In this case it is a stored proc. The stored proc itself inserts a record into a queue which is being monitored by the sql agent loop job. Once the job sees an new entry in the queue it will check if the requested job isn’t already requested. If it isn’t requested before, it will then check if the requesting user has rights in the userjob mapping table.

Current version does not support user enabled scheduling. I am working on that. Also a job monitoring function will be added in future version

 

 

----------------------------------------------------------------------------------------------------------------------------------------------
--     Job Queue Proxy 
----------------------------------------------------------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium
--     Created:     2011-04-01
--     Database:    New database
--     Schema:      dbo
--     Type:        script
--     Purpose:     Create a low level / high level security proxy to handle user requests to start sql server agent jobs. 
--                    Concept is based on the idea that users only should be allowed to have read and write access to table 
--                    by using stored procedures. The job request insert stored procedure is a low level security & risk,
--                    which all alllowed users should have access to and the right to execute this procedure.
--                    The Job that checks the queue runs on a high security level and therefor is able to start the requested sql agent jobs.
--
--                    Many thanks to Theo Ekelmans for his aid and tips for this solution
----------------------------------------------------------------------------------------------------------------------------------------------
--    Install Instruction
--
--    Edit the @mdfpath and @logpath parameters to the correct filepaths
--
--    Execute the script
--
--    To obtain available jobnames for the UserJobMapping table:
--    select * from msdb.dbo.sysjobs
--
--    To obtain user_id's for the UserJobMapping table:
--    select * from sys.server_principals
--
--    With these 2 queries you should be able to set the correct user to job mappings.
--
----------------------------------------------------------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0          2011-04-11   Initial
----------------------------------------------------------------------------------------------------------------------------------------------
USE [master]
go


declare @mdfpath nvarchar(250)
declare @logpath nvarchar(250)
declare @createstring nvarchar(max)

set @mdfpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' -- Edit to the correct file path
set @logpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' -- Edit to the correct file path

-- NO Editing past this point ----------------------------------------------------------------------------------------------------------------

-- Database creation --
set @mdfpath = @mdfpath + N'JobExecution.mdf'
set @logpath = @logpath + N'JobExecution_log.ldf'

set @createstring = 'CREATE DATABASE [JobExecution] ON  PRIMARY '
set @createstring = @createstring + '( NAME = N''JobExecution'', FILENAME =N''' + @mdfpath + ''' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
set @createstring = @createstring + ' LOG ON '
set @createstring = @createstring + '( NAME = N''JobExecution_log'', FILENAME = N''' + @logpath + ''' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) '

exec sp_executesql  @createstring
GO

ALTER DATABASE [JobExecution] SET COMPATIBILITY_LEVEL = 100
GO

USE [JobExecution]
GO

-- JobQueue table creation --
CREATE TABLE [dbo].[JobQueue](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [created] [datetime] NULL,
       [job_type] [int] NULL,
       [inprocess] [int] NOT NULL,
       [finished] [int] NOT NULL,
       [job_name] [varchar](250) NULL,
       [prio] [int] NULL,
       [user_id] [int] NULL,
       [runat] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_inprocess]  DEFAULT ((0)) FOR [inprocess]
GO
ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_finished]  DEFAULT ((0)) FOR [finished]
GO
ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_prio]  DEFAULT ((0)) FOR [prio]
GO




-- UserJobMapping table creation
CREATE TABLE [dbo].[UserJobMapping](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [user_id] [int] NULL,
       [job_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

--------------------------------------------------------------------------------------------
--     Queue control 
--------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium & Theo Ekelmans
--     Created:     2011-04-01
--     Database:    JobExecution
--     Schema:      dbo
--     Type:        Stored Procedure
--     Purpose:     Check if there is a job in the queue and execute based on set rights and prevent hammering
--------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0          2011-04-11   Initial
--------------------------------------------------------------------------------------------
create proc [dbo].[spQueueCheck]

as

declare @count int
declare @id int
declare @jobid uniqueidentifier
declare @right int
declare @hammering int

set @count = (select COUNT(1) from JobQueue where [inprocess] = 0)

if @count > 0
BEGIN

set @id = (select top 1 id from JobQueue where [inprocess] = 0)

set @hammering = (select COUNT(1) from JobQueue where job_name = (select job_name from JobQueue where ID = @id) and [inprocess] = 0)

if @hammering < 1

       BEGIN

       set @jobid = (select job_id from msdb.dbo.sysjobs where name = (select job_id from JobQueue where ID = (select id from JobQueue where [inprocess] = 0)))

       set @right = (select COUNT(1) from userjobmapping ujm inner join JobQueue w on ujm.user_id = w.user_id and ujm.job_id = @jobid)

       if @right > 0
             BEGIN
             
             EXEC msdb.dbo.sp_start_job @jobid
             
             END
       
       END

END
GO
--------------------------------------------------------------------------------------------
--     Insert Job into Queue 
--------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium & Theo Ekelmans
--     Created:     2011-04-01
--     Database:    JobExecution
--     Schema:      dbo
--     Type:        Stored Procedure
--     Purpose:     Insert a job into the queue
--------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0                 2011-04-11   Initial
--------------------------------------------------------------------------------------------
CREATE proc [dbo].[spInsertJobExecution]
@job_type int
,@job_name varchar(250)
,@prio int
,@runat datetime

AS
       BEGIN
            if @runat > GETDATE()
       

             INSERT INTO [JobExecution].[dbo].[JobQueue]
                    (
                    [created]
                    ,[job_type]
                    ,[job_name]
                    ,[prio]
                    ,[user_id]
                    )
             VALUES
                    (
                    GETDATE(),
                    @job_type,
                    @job_name,
                    @prio,
                    SYSTEM_USER
                    )
       END
GO

-- Create Job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'JobExecutionJob', 
             @enabled=1, 
             @notify_level_eventlog=2, 
             @notify_level_email=0, 
             @notify_level_netsend=0, 
             @notify_level_page=0, 
             @delete_level=0, 
             @description=N'SQL Loop job to check if there is a job request in the queue', 
             @category_name=N'[Uncategorized (Local)]', 
             @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Stap 1 : Controle]    Script Date: 04/11/2011 16:37:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1 : Check', 
             @step_id=1, 
             @cmdexec_success_code=0, 
             @on_success_action=4, 
             @on_success_step_id=2, 
             @on_fail_action=2, 
             @on_fail_step_id=0, 
             @retry_attempts=0, 
             @retry_interval=0, 
             @os_run_priority=0, @subsystem=N'TSQL', 
             @command=N'exec spQueueCheck', 
             @database_name=N'JobExecution', 
             @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Stap 2 : Loop]    Script Date: 04/11/2011 16:37:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 2 : Loop', 
             @step_id=2, 
             @cmdexec_success_code=0, 
             @on_success_action=4, 
             @on_success_step_id=1, 
             @on_fail_action=2, 
             @on_fail_step_id=0, 
             @retry_attempts=0, 
             @retry_interval=0, 
             @os_run_priority=0, @subsystem=N'TSQL', 
             @command=N'WAITFOR DELAY ''00:00:05''


', 
             @database_name=N'master', 
             @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

exec msdb.dbo.sp_start_job 'JobExecutionJob'
go



Posted On Thursday, May 19, 2011 6:32 PM | Comments (0)