I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):
ALTER PROCEDURE [dbo].[RunJob]
@job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
--SET NOCOUNT ON;
BEGIN TRY
EXEC msdb.dbo.sp_start_job @job_name = @job_name
-- Wait for job to finish
DECLARE @job_history_id AS INT = NULL
DECLARE @job_result AS INT = NULL
WHILE 1=1
BEGIN
SELECT TOP 1 @job_history_id = activity.job_history_id
FROM msdb.dbo.sysjobs jobs
INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
WHERE jobs.name = @job_name
ORDER BY activity.start_execution_date DESC
IF @job_history_id IS NULL
BEGIN
WAITFOR DELAY '00:00:01'
CONTINUE
END
ELSE
BREAK
END
-- Check exit code
SET @job_result = (SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id)
RETURN @job_result;
END TRY
BEGIN CATCH
THROW;
RETURN;
END CATCH
END
However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:
Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1 The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job
.
How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?