Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Start SQL Agent job using stored procedure Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:06 PM
Points: 3, Visits: 77
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
Post #1563942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse