Start SQL Agent job using stored procedure

• todor.belchev

  Valued Member

  Points: 55

  More actions

  April 22, 2014 at 10:04 am

  #145012

  Hi,

  I'm trying to execute a stored procedure which will start a SQL Agent Job but I got "The SELECT permission was denied on the object 'sysjobactivity', database 'msdb', schema 'dbo'". I know that the db_owner can run the query below but this is not the case. I need the following query to be executed using a SQL Agent Proxy account but what permissions/roles this account should has?

  DECLARE @SQLJobHistoryID bigint

  DECLARE @status nvarchar(100)

  SET @status = 'Running'

  -- check if @Action is passed. If is not then get the default value - check

  IF @Action IS NULL

  BEGIN

  SET @Action = 'check'

  END

  -- select last history id. if the job is running at the moment the id is NULL

  SELECT TOP 1 @SQLJobHistoryID = 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 = @SQLJobName

  ORDER BY activity.start_execution_date DESC

  -- if @SQLJobHistoryID IS NULL than the job is running and we cannot run it again until the job finish

  IF @Action = 'run' AND @SQLJobHistoryID IS NOT NULL

  BEGIN

  IF EXISTS ( SELECT * FROM msdb.dbo.sysjobs WHERE name = @SQLJobName)

  BEGIN

  EXEC msdb.dbo.sp_start_job @SQLJobName

  SELECT @status as [Status]

  RETURN

  END

  END

  SELECT

  @status = CASE

  WHEN run_status = 0 THEN 'Failed'

  WHEN run_status = 1 THEN 'Succeeded'

  WHEN run_status = 2 THEN 'Retry'

  WHEN run_status = 3 THEN 'Canceled'

  END

  FROM msdb.dbo.sysjobhistory history

  WHERE history.instance_id = @SQLJobHistoryID

  SELECT @status AS [Status]

  Thanks