It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own.
EDIT: For clarity on the three options presented by explicitly mentioning the SQLAgentOperatorRole as an option and by adding some explanation on the third solution.
(1) If the user is allowed to manage the execution of all jobs, then make that user member of SQLAgentOperatorRole. The user will be able to start (as well as stop, enable, and disable) any SQL Agent job on that server. (This solution turned out to satisfy the original asker.)
(2) Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures. He has a solution at:
The key point is: "To be able to start a job owned by someone else, you need to be member of the fixed role
msdb. A start is to write a stored procedure that calls
sp_start_job for this specific job, sign that procedure with a certificate, and then create a user from the certificate and make that user a member of
(3) My general resolution was to create a
StartAgentJob stored procedure in the
msdb database allowing a user to start jobs owned by someone else.
This requires a table to maintain the configuration of who can run which job. Since the following
dbo.msdbJobMap table is SQL Server Agent Job specific, I would create the table in
msdb. But it could be created in some other service database if desired.
/* Create a table to hold configuration of who can start jobs. */
CREATE TABLE dbo.msdbJobMap
/* Populate the table of allowed groups for a job
A group may be a single user or a Windows group. */
INSERT INTO dbo.msdbJobMap Values (N'Test it out',N'Domain\Group');
INSERT INTO dbo.msdbJobMap Values (N'Another job',N'Domain\OtherGroup');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Joe');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Andre');
The stored procedure also allows any member of a specified group to start a job since it uses
IS_MEMBER to check group membership.
CREATE PROCEDURE dbo.StartAgentJob
WITH EXECUTE AS OWNER
SET NOCOUNT ON;
DECLARE @Allowed INT;
SET @Allowed = 0;
/* Since this runs as sysadmin need to check group membership of original login*/
EXECUTE AS LOGIN = ORIGINAL_LOGIN();
IF EXISTS (SELECT * FROM dbo.msdbJobMap
WHERE job_name = @Job_Name
AND IS_MEMBER(group_name) = 1 )
SET @Allowed = 1;
/* Back to sysadmin so that we can start the job. */
IF @Allowed = 1
EXEC sp_start_job @job_name = @Job_Name;
PRINT 'Invalid attempt to start ''' + QUOTENAME(@Job_Name)+'''';
As you can see, the procedure depends on running as
msdb. By switching to the context of the
ORIGINAL_LOGIN it is able to use
IS_MEMBER to check that the
ORIGINAL_LOGIN has indeed been granted rights through the
dbo.msdbJobMap table. Then it goes back to being
sysadmin so that it can start the job.