How do I let someone execute a SQL Agent job they don’t own?

In my last post I discussed the fact that SQL Ids can, in fact, be useful on an instance that is set to Windows Authentication only. Today I’m going to use that same technique to give someone permission to run a SQL Server Agent job without granting them excessive permissions.

Quick background. There are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole (found in msdb). Unfortunately SQLAgentOperatorRole grants permissions to run any job (among other things).

However, by creating an id with the permissions to run any job and then creating a stored procedure that uses impersonation to use that id to run the job we can work around this.

USE msdb;
GO
-- Create a user to run the job.
CREATE USER CanRunAnyJob WITHOUT LOGIN;
-- Add user CanRunAnyJob to the role SQLAgentOperatorRole
ALTER ROLE SQLAgentOperatorRole ADD MEMBER CanRunAnyJob;
GO
-- Create a stored procedure that executes the job
CREATE PROCEDURE sp_RunThisJob
WITH EXECUTE AS 'CanRunAnyJob'
AS
EXEC sp_start_job 'ThisJob';
GO

Now, in order to test we have two options. Either test using a windows/AD id by running SSMS as an alternate ID or create another SQL Id and use impersonation again. I’m using the SQL Id here but I did test this both ways and it worked fine.

-- Create a SQL Id with no permissions other than to run 
-- the stored procedure.
USE msdb;
GO
CREATE USER CanRunAJob WITHOUT LOGIN;
GO
GRANT EXECUTE ON sp_RunThisJob TO CanRunAJob;
GO
-- Impersonate the user and run the job.
EXECUTE AS USER = 'CanRunAJob';
GO
EXECUTE sp_RunThisJob;
GO
REVERT;

A few things of note here.

• The job I used was a very simple job. It is owned by sa with a single step that ran a single T-SQL statement:

  SELECT name FROM sys.databases;

  I believe this will work regardless of the owner of the job or the tasks the job is running, however, if you run into problems remember that proxies will allow you to specify exactly who is running each step of a job.

• The SQL Ids I created didn’t have an associated server principal (login). This means that there is no password, no instance level CONNECT SQL permission, etc. Basically, regardless of if the instance is in Mixed mode or Windows Authenticated mode you can’t use these ids to connect to the instance. I did this deliberately to limit any security risks.
• Everything I did here was in msdb. This is because the permissions needed were all there. When dealing with this in a real environment your CanRunAnyJob user will have to be created in msdb so that it can be added to the SQLAgentOperatorRole role. That means the stored procedure also has to be created in msdb. And finally, anyone that is going to have access to run that stored procedure will need a user within msdb. I don’t see this as a major risk since the only permissions they will be granted is EXECUTE on that stored procedure.