I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the
SQLAgentOperatorRole role in msdb as that will give them excess priveleges.
I tried creating a stored procedure which will run as the owner (dbo):
Create the stored procedure (logged in with a sysadmin account)
CREATE LOGIN [UnPriveleged] WITH PASSWORD = 'MyPassword' GO USE AdventureWorks2017 GO CREATE USER UnPriveleged FOR LOGIN [UnPriveleged] GO CREATE PROCEDURE spRunJOb WITH EXECUTE AS OWNER AS SELECT CURRENT_USER SELECT SYSTEM_USER EXEC msdb..sp_start_job @job_name = 'test' GO GRANT EXECUTE ON spRunJOb TO UnPriveleged
and then I run it as the Unpriveleged user in another session:
SELECT CURRENT_USER EXECUTE [spRunJOb]
and I get an error
Msg 229, Level 14, State 5, Procedure msdb..sp_start_job, Line 1 [Batch Start Line 0] The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
I thought this would run as the login for the owner of the stored procedure (sa) as that is a sysadmin and should be able to do everything.
The output I get from the selects confirms the procedure is executing as the sa system user. The overall SELECT output is
Unpriveleged dbo sa
How can I achieve what I want here?