I'm trying to run a stored procedure which starts up a job. When I run it as myself (as a sysadmin), it runs without a hitch, but when I run it as a custom SQL Server user ("SQLAgentUser" -- mapped to a SQL Server login), it gives the error:
EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
SQLAgentUser is a member of all the following roles in msdb:
- SQLAgentOperatorRole
- SQLAgentReaderRole
- SQLAgentUserRole
In addition, SQLAgentUser has the following explicit permissions granted in MSDB:
- GRANT EXECUTE on sp_start_job (and when I look at effective permissions in SSMS, it says it has Execute permissions)
- GRANT EXECUTE on sp_stop_job (same as above)
In MyDb (not its actual name), SQLAgentUser has EXECUTE permissions to the sqladm schema, as well as DELETE, INSERT, SELECT, and UPDATE permissions to sqladm.AgentJobsLastRun.
According to everything I found online, this SHOULD be all that's needed, but I'm still getting the error when executing as SQLAgentUser.
Here's the erroring code:
USE [MyDb]
GO
DECLARE @RC int
EXECUTE AS USER = 'SQLAgentUser'
UPDATE [sqladm].[AgentJobsLastRun]
SET RunDate = NULL
WHERE JobName = 'MonthlyJobs'
EXECUTE @RC = [sqladm].[udp_DailyJob]
REVERT
GO
If I comment out, "EXECUTE AS USER = 'SQLAgentUser'," it runs without a hitch.
...and the code inside [sqladm].[udp_DailyJob]:
SET NOCOUNT ON;
-- Insert statements for procedure here
-- First check that the monthly job has run this month (should have run at 2 AM on the first). If not, manually run the job.
DECLARE @AgentJobNameSys nvarchar(128) = N'MonthlyJobs'
DECLARE @LastRunDate datetime = COALESCE((SELECT [RunDate] FROM [sqladm].[AgentJobsLastRun] WHERE JobName = @AgentJobNameSys),DATEADD(month,-1,getdate()))
IF @LastRunDate < DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1)
BEGIN
EXEC msdb.dbo.sp_start_job @AgentJobNameSys;
END
Running SQL Server 2019 Developer Edition on my local computer. Many thanks! :)