Allow Non-Sysadmin, Non-Owner to Execute SQL Server Agent Job

jobspermissionssql serverssis

I have a job that runs an SSIS package.

Currently the owner is a proxy account. I can run the job manually from a sys-admin account.

Our web service logs in using a limited account. It needs to execute the job.
Currently it is unable to see the job at all(when I try to execute by name, it says it doesn't exist).

I tried changing to owner of the job to the limited account. Now it could see the job, but the job failed execution because it can't run the SSIS package anymore.

There has to be a way to allow the limited account to run a job owned by another account right?

Best Answer

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.

Related Question