Sample Header Ad - 728x90

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