Sql-server – Giving access to only one SQL Agent Job

Please check to see if there is a DENY on the sp_update_job procedure. This may be a direct DENY to your login or it may be a DENY on some group that has your login as a member. DENY always trumps a grant.

If so, then removing your login from the group or having an administrator REVOKE the DENY should solve the problem.

Response prompted by reading Piers7 request. Time has passed, of course.

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:

http://www.sommarskog.se/grantperm.html#countersignatures

The key point is: "To be able to start a job owned by someone else, you need to be member of the fixed role SQLAgentOperatorRole in 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 SQLAgentOperatorRole."

(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.

USE msdb;

/* Create a table to hold configuration of who can start jobs. */
CREATE TABLE dbo.msdbJobMap  
 (job_name NVARCHAR(128),
  group_name NVARCHAR(256));

/* 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
@Job_Name NVARCHAR(128)
WITH EXECUTE AS OWNER
AS
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;
REVERT;

/* Back to sysadmin so that we can start the job. */

IF @Allowed = 1 
    EXEC sp_start_job @job_name = @Job_Name;
ELSE
    PRINT 'Invalid attempt to start ''' + QUOTENAME(@Job_Name)+'''';
RETURN;

As you can see, the procedure depends on running as sysadmin in 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.