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?
Best Answer
The issue here is cross-database ownership chaining, basically once you leave the context of the AdventureWorks database, you don't have the elevated rights that dbo/sa implies and cannot actually execute the procedure in msdb.
You should recreate the stored procedure in msdb instead and grant your unprivileged user CONNECT permission to msdb and EXECUTE permission on the new procedure in msdb.
Alternatively, you can turn TRUSTWORTHY on for the AdventureWorks database but, generally, that is ill-advised. See this answer from Aaron Bertrand for additional info and a number of great links explaining the risks of TRUSTWORTHY.