2

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?

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.