Allowing users to start and stop SQL Server Agent Jobs

Start/StopI recently received a request to allow certain users to start and stop SQL Server Agent jobs, without giving them the ability to use the SQL Server Agent tools in SQL Server Management Studio.

I created a Visual Studio .Net application that provides that ability through stored procedures that use the WITH EXECUTE AS OWNER directive.

The T-SQL code for the SQL Server prerequisites for this app are as follows:

/*
	Script to create objects required for the Job Control App
	By:		Max Vernon
	Date:	2016-04-27
	
	Notes:	Compatible with SQL Server 2005+ (not tested on 2014+)
*/
USE msdb;
GO
IF OBJECT_ID('dbo.sysjobs_log') IS NOT NULL
DROP TABLE dbo.sysjobs_log;
GO
IF OBJECT_ID('dbo.sysjobs_action_types') IS NOT NULL
DROP TABLE dbo.sysjobs_action_types;
GO
CREATE TABLE dbo.sysjobs_action_types
(
	ActionTypeID INT NOT NULL
		CONSTRAINT PK_sysjobs_action_types
		PRIMARY KEY
		CLUSTERED
	, ActionDescription VARCHAR(100) NOT NULL
) ON [PRIMARY];
INSERT INTO dbo.sysjobs_action_types (ActionTypeID, ActionDescription)
VALUES (1, 'Started Job')
INSERT INTO dbo.sysjobs_action_types (ActionTypeID, ActionDescription)
VALUES (2, 'Stopped Job');
GO
CREATE TABLE dbo.sysjobs_log
(
	sysjobs_log_ID INT NOT NULL
		CONSTRAINT PK_sysjobs_log
		PRIMARY KEY 
		CLUSTERED
		IDENTITY(1,1)
	, job_id UNIQUEIDENTIFIER NOT NULL
		CONSTRAINT FK_sysjobs_log__job_id
		FOREIGN KEY
		REFERENCES dbo.sysjobs(job_id)
		ON DELETE CASCADE
	, JobActionDateTime DATETIME NOT NULL
		CONSTRAINT DF_sysjobs_log_JobActionDateTime
		DEFAULT (GETDATE())
	, ActionBy SYSNAME NOT NULL
		CONSTRAINT DF_sysjobs_log_StartedBy
		DEFAULT (ORIGINAL_LOGIN())
	, ActionAs SYSNAME NOT NULL
		CONSTRAINT DF_sysjobs_log_StartedAs
		DEFAULT (SUSER_SNAME())
	, ActionTypeID INT NOT NULL
		CONSTRAINT FK_sysjobs_log__ActionType
		FOREIGN KEY
		REFERENCES dbo.sysjobs_action_types(ActionTypeID)
) ON [PRIMARY];
GO
IF OBJECT_ID('dbo.sysjobs_filter') IS NOT NULL
DROP TABLE dbo.sysjobs_filter;
GO
CREATE TABLE dbo.sysjobs_filter
(
	job_id UNIQUEIDENTIFIER NOT NULL
		CONSTRAINT FK_sysjobs_filter__job_id
		FOREIGN KEY
		REFERENCES dbo.sysjobs(job_id)
		ON DELETE CASCADE
) ON [PRIMARY];
CREATE INDEX PK_sysjobs_filter
ON dbo.sysjobs_filter(job_id);
GO
IF OBJECT_ID('dbo.cp_s_job_list') IS NOT NULL
DROP PROCEDURE dbo.cp_s_job_list;
GO
/************************************************************
Shows a list of jobs that are available for non-DBAs to run
using the dbo.cp_s_start_job interface

By:			Max Vernon
Date:		2016-04-25

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_s_job_list
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	SELECT JobID = sj.job_id
		, JobName = sj.name
		, JobDescription = sj.description
		, IsEnabled = CASE WHEN sj.enabled = 1 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END
		, LastRunDate = (SELECT TOP(1) msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time)
			FROM msdb.dbo.sysjobhistory sjh
			WHERE sjh.job_id = sj.job_id
			ORDER BY sjh.run_date DESC, sjh.run_time DESC)
	FROM msdb.dbo.sysjobs sj
		INNER JOIN msdb.dbo.sysjobs_filter sjf ON sj.job_id = sjf.job_id
	ORDER BY sj.name;
END
GO
IF OBJECT_ID('dbo.cp_s_job_status') IS NOT NULL
DROP PROCEDURE dbo.cp_s_job_status;
GO
/************************************************************
Gets the status of the specified Job

By:			Max Vernon
Date:		2016-04-25

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_s_job_status
(
	@JobID UNIQUEIDENTIFIER
)
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs_filter sjf WHERE sjf.job_id = @JobID)
	BEGIN

	DECLARE @can_see_all_running_jobs INT
	DECLARE @job_owner   sysname

	DECLARE @job_execution_state TABLE 
	(
		  job_id                  UNIQUEIDENTIFIER NOT NULL
		, date_started            INT              NOT NULL
		, time_started            INT              NOT NULL
		, execution_job_status    INT              NOT NULL
		, execution_step_id       INT              NULL
		, execution_step_name     sysname          COLLATE database_default NULL
		, execution_retry_attempt INT              NOT NULL
		, next_run_date           INT              NOT NULL
		, next_run_time           INT              NOT NULL
		, next_run_schedule_id    INT              NOT NULL
	);

	DECLARE @filtered_jobs TABLE 
	(
		  job_id                   UNIQUEIDENTIFIER NOT NULL
		, date_created             DATETIME         NOT NULL
		, date_last_modified       DATETIME         NOT NULL
		, current_execution_status INT              NULL
		, current_execution_step   INT		        NULL
		, current_execution_step_name sysname       COLLATE database_default NULL
		, current_retry_attempt    INT              NULL
		, last_run_date            INT              NOT NULL
		, last_run_time            INT              NOT NULL
		, last_run_outcome         INT              NOT NULL
		, next_run_date            INT              NULL
		, next_run_time            INT              NULL
		, next_run_schedule_id     INT              NULL
		, type                     INT              NOT NULL
	);

	DECLARE @xp_results TABLE 
	(
		  job_id                UNIQUEIDENTIFIER NOT NULL
		, last_run_date         INT              NOT NULL
		, last_run_time         INT              NOT NULL
		, next_run_date         INT              NOT NULL
		, next_run_time         INT              NOT NULL
		, next_run_schedule_id  INT              NOT NULL
		, requested_to_run      INT              NOT NULL -- BOOL
		, request_source        INT              NOT NULL
		, request_source_id     sysname          COLLATE database_default NULL
		, running               INT              NOT NULL -- BOOL
		, current_step          INT              NOT NULL
		, current_retry_attempt INT              NOT NULL
		, job_state             INT              NOT NULL
	);

	SET @can_see_all_running_jobs = 1;
	SELECT @job_owner = SUSER_SNAME();

	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @JobID;

	INSERT INTO @job_execution_state
	SELECT xpr.job_id
		, xpr.last_run_date
		, xpr.last_run_time
		, xpr.job_state
		, sjs.step_id
		, sjs.step_name
		, xpr.current_retry_attempt
		, xpr.next_run_date
		, xpr.next_run_time
		, xpr.next_run_schedule_id
	FROM @xp_results xpr
		LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
	msdb.dbo.sysjobs_view sjv
	WHERE (sjv.job_id = xpr.job_id);

	INSERT INTO @filtered_jobs
	SELECT sjv.job_id
		, sjv.date_created
		, sjv.date_modified
		, ISNULL(jes.execution_job_status, 4) -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)
		, current_execution_step = jes.execution_step_id
		, current_execution_step_name = jes.execution_step_name
		, jes.execution_retry_attempt         -- Will be NULL if the job is non-local or is not in @job_execution_state
		, 0  -- last_run_date placeholder    
		, 0  -- last_run_time placeholder    
		, 5  -- last_run_outcome placeholder 
		, jes.next_run_date                   -- Will be NULL if the job is non-local or is not in @job_execution_state
		, jes.next_run_time                   -- Will be NULL if the job is non-local or is not in @job_execution_state
		, jes.next_run_schedule_id            -- Will be NULL if the job is non-local or is not in @job_execution_state
		, 0   -- type placeholder
	FROM msdb.dbo.sysjobs_view sjv
		LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)
	WHERE sjv.job_id = @JobID;

	UPDATE @filtered_jobs
	SET current_execution_status = NULL
	WHERE (current_execution_status = 4)
		AND (job_id IN (
			SELECT job_id
			FROM msdb.dbo.sysjobservers
			WHERE (server_id <> 0))
			);

	UPDATE @filtered_jobs
	SET last_run_date = sjs.last_run_date,
		last_run_time = sjs.last_run_time,
		last_run_outcome = sjs.last_run_outcome
	FROM @filtered_jobs         fj,
		msdb.dbo.sysjobservers sjs
	WHERE (fj.job_id = sjs.job_id);

	UPDATE @filtered_jobs
	SET type = 1 -- LOCAL
	FROM @filtered_jobs fj,
		msdb.dbo.sysjobservers sjs
	WHERE (fj.job_id = sjs.job_id)
		AND (server_id = 0);
	UPDATE @filtered_jobs
	SET type = 2 -- MULTI-SERVER
	FROM @filtered_jobs fj,
		msdb.dbo.sysjobservers sjs
	WHERE (fj.job_id = sjs.job_id)
		AND (server_id <> 0);

	SELECT JobID = sjv.job_id
		, JobName = sjv.name
		, JobEnabled = sjv.enabled
		, JobDescription = sjv.description
		, StartStepID = sjv.start_step_id
		, last_run_date_time = CASE WHEN fj.last_run_date > 0 THEN msdb.dbo.agent_datetime(fj.last_run_date, fj.last_run_time) ELSE NULL END
		, fj.last_run_outcome
		, current_execution_status = COALESCE(fj.current_execution_status, 0)
		, current_execution_step = COALESCE(fj.current_execution_step, 0)
		, current_execution_step_name = fj.current_execution_step_name
		, current_retry_attempt = COALESCE(fj.current_retry_attempt, 0)
	FROM @filtered_jobs fj
	LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
	ORDER BY sjv.job_id;

	END
	ELSE
	BEGIN
		DECLARE @msg NVARCHAR(1000);
		SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.';
		RAISERROR (@msg, 11, 1);
	END
END
GO
IF OBJECT_ID('dbo.cp_e_start_job') IS NOT NULL
DROP PROCEDURE dbo.cp_e_start_job;
GO
/************************************************************
Starts the specified Job

By:			Max Vernon
Date:		2016-04-25

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_e_start_job
(
	@JobID UNIQUEIDENTIFIER
)
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @msg NVARCHAR(1000);
	IF EXISTS (
		SELECT 1
		FROM dbo.sysjobs_filter sjmf
			INNER JOIN dbo.sysjobs sj ON sjmf.job_id = sj.job_id
		WHERE sjmf.job_id = @JobID
		)
	BEGIN
		INSERT INTO dbo.sysjobs_log (job_id, ActionTypeID)
		VALUES (@JobID, 1); --1 is "Started Job"
		EXEC dbo.sp_start_job @job_id = @JobID;
	END
	ELSE
	BEGIN
		SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.';
		RAISERROR (@msg, 11, 1);
	END
END
GO
IF OBJECT_ID('dbo.cp_e_stop_job') IS NOT NULL
DROP PROCEDURE dbo.cp_e_stop_job;
GO
/************************************************************
Stops the specified Job

By:			Max Vernon
Date:		2016-04-25

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_e_stop_job
(
	@JobID UNIQUEIDENTIFIER
)
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @msg NVARCHAR(1000);
	IF EXISTS (
		SELECT 1
		FROM dbo.sysjobs_filter sjmf
			INNER JOIN dbo.sysjobs sj ON sjmf.job_id = sj.job_id
		WHERE sjmf.job_id = @JobID
		)
	BEGIN
		INSERT INTO dbo.sysjobs_log (job_id, ActionTypeID)
		VALUES (@JobID, 2); --1 is "Started Job"
		EXEC dbo.sp_stop_job @job_id = @JobID;
	END
	ELSE
	BEGIN
		SET @msg = CONVERT(NVARCHAR(100), @JobID) + ' is not a valid job identifier.';
		RAISERROR (@msg, 11, 1);
	END
END
GO
IF OBJECT_ID('dbo.cp_s_job_control_perms') IS NOT NULL
DROP PROCEDURE dbo.cp_s_job_control_perms;
GO
/************************************************************
Confirms the objects exist and the determines the callers
security level for the SQL Agent Job Control system.

By:			Max Vernon
Date:		2016-04-27

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_s_job_control_perms
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @OutputList TABLE
	(
		ObjectName SYSNAME NOT NULL
		, ObjectVal INT NOT NULL
	);
	INSERT INTO @OutputList (ObjectName, ObjectVal)
	SELECT ObjectName = 'dbo.cp_s_job_list', ObjectID = COALESCE(OBJECT_ID('dbo.cp_s_job_list'), 0)
	UNION ALL
	SELECT 'dbo.cp_s_job_status', COALESCE(OBJECT_ID('dbo.cp_s_job_status'), 0)
	UNION ALL
	SELECT 'dbo.cp_e_stop_job', COALESCE(OBJECT_ID('dbo.cp_e_stop_job'), 0)
	UNION ALL
	SELECT 'dbo.cp_e_start_job', COALESCE(OBJECT_ID('dbo.cp_e_start_job'), 0)
	
	EXECUTE AS CALLER;
	INSERT INTO @OutputList (ObjectName, ObjectVal)
	SELECT 'ControlPermissions', (
		SELECT COUNT(1) 
		FROM sys.fn_my_permissions('dbo.sysjobs', 'OBJECT') p 
		WHERE p.permission_name = 'CONTROL'
		);
	REVERT;

	SELECT *
	FROM @OutputList;
END
GO
IF OBJECT_ID('dbo.cp_s_get_job_outcome') IS NOT NULL
DROP PROCEDURE dbo.cp_s_get_job_outcome;
GO
/************************************************************
Gets the most recent history item for the specified job


By:			Max Vernon
Date:		2016-04-27

Version:	1.0		Initial Version
************************************************************/
CREATE PROCEDURE dbo.cp_s_get_job_outcome
(
	@JobID UNIQUEIDENTIFIER
)
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP(1) sj.job_id
		  , [Job Name] = sj.name
		  , [Step Name] = sjh.step_name 
		  , sjh.message
	FROM msdb.dbo.sysjobs sj 
		  INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
	WHERE sj.job_id = @JobID
	ORDER BY msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) DESC
		, sjh.step_id;
END
GO

IF NOT EXISTS (
	SELECT 1 
	FROM sys.database_principals dp 
	WHERE dp.name = 'agent_job_control'
	)
CREATE ROLE agent_job_control AUTHORIZATION [dbo];
GRANT EXECUTE ON dbo.cp_s_job_list TO agent_job_control;
GRANT EXECUTE ON dbo.cp_s_job_status TO agent_job_control;
GRANT EXECUTE ON dbo.cp_e_start_job TO agent_job_control;
GRANT EXECUTE ON dbo.cp_e_stop_job TO agent_job_control;
GRANT EXECUTE ON dbo.cp_s_job_control_perms TO agent_job_control;
GRANT EXECUTE ON dbo.cp_s_get_job_outcome TO agent_job_control;
GO

You’ll need to create user principals in the msdb object, and add them to the agent_job_control database role. Once that is completed those users can start and stop jobs, and obtain the status and recent history of jobs, by specifying the job_id uniqueidentifier.

Project Code is here: SQLServerJobControl