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

2

April 15, 2019 by Kenneth Fisher

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.

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

  1. I also used to give different app groups a webpage they could use. I would put their list of jobs in a dropdown and they could just run them from there. That way they don’t have to know SQL at all.

    • Oh certainly. It can even be expanded. Create a table with AD groups and what jobs they are allowed to run. Even what jobs they can edit and modify the owner of the job so they can then go in and edit the job. You’d want a second job to “reset” the job owner before it runs but it would help get around the “no AD group as job owner” problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,084 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: