How do I let someone execute a SQL Agent job they don’t own?
12April 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.
SQL Studies 
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.
Hi Kenneth! Is it possible to see the list of specific jobs in the SSMS? I want my users to start the jobs from SSMS via SQL server agent. Lets say that I have 3 users and 1 job named ThreeUsers only for them, another 2 users which has 1 job named TwoUsers. I want the job named ThreeUsers to be seen only from its 3 users. I want all of the other jobs to be hidden for the specific users. Is it possible to do it somehow?
If you do something like what Sean was talking about but I don’t think so directly through SSMS I’m afraid.
[…] How do I let someone execute a SQL Agent job they don’t own? Last modified: October 12, 2020 […]
Is it possible to grant permission to run based on category?
Here is code to to see the concept, it does not work or I would have hours back in my week.
GRANT EXECUTE ON (SELECT *
FROM [msdb].[dbo].[sysjobs_view]
where [category_id] =’5150′) TO [LuckyUser]
Unfortunately no. Like I said, you can’t even grant execute for a specific job let alone a category of them. However, you could create a SP uses execute as to start the job the job using a specific id that has generic access and then handle security yourself.
I set your solution up and when I tested it I got the following error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal “” does not exist, this type of principal cannot be impersonated, or you do not have permission.
However, the requested job did started..
because there is not login I cannot have one login impersonate the msdb user.
wondering how I might make this error not happen.
I’m curious exactly how you are doing this. The “” tends to lead me to believe there is a problem somewhere. However, you can do an EXECUTE AS USER = ‘username’ even if there isn’t a login.
i THINK that i did the same thing. at least i got a similar error when i told a user to run this, and like you, the job still kicked off and ran successfully:
USE msdb;
go
EXECUTE AS USER = ‘RunJobs’;
GO
EXECUTE sp_RestoreDBAJOBS;
GO
REVERT;
EXECUTE AS USER = ‘RunJobs’; is only needed when testing it bc you likely have elevated permissions. the impersonation in the sp is the main thing. it worked for my user in SSMS without the EXECUTE AS USER and it didn’t throw an error.
btw – this is exactly what i needed to fix my problem! thank you for posting.
Glad to hear it 🙂 It’s a pain in the neck problem but one that comes up a lot.