Allow two users permission to execute single agent job
2
votes
1
answer
851
views
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?
Asked by SE1986
(2182 rep)
Aug 22, 2019, 09:17 PM
Last activity: Aug 23, 2019, 04:02 AM
Last activity: Aug 23, 2019, 04:02 AM