Permissions issue while running SqlAgent Job

Question

When I try to run a Sql agent job, I am making a check that, there are no prior instances running at the same time. The query is given below:

IF NOT EXISTS(SELECT
                            1
                        FROM msdb.dbo.sysjobactivity ja 
                        LEFT JOIN msdb.dbo.sysjobhistory jh 
                            ON ja.job_history_id = jh.instance_id
                        JOIN msdb.dbo.sysjobs j 
                        ON ja.job_id = j.job_id
                        JOIN msdb.dbo.sysjobsteps js
                            ON ja.job_id = js.job_id
                            AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
                        WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
                        AND j.name = N'JobName' 
                        AND start_execution_date is not null
                        AND stop_execution_date is null
                 )
                BEGIN   
                -- code to run the job
                END

But, I am getting an error that

The SELECT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).

The step failed. The job step is run by an account with sysadmin privileges.

I tried to give the account with db_owner privilege in msdb database also. Still, facing the same issue.

Can you please guide me, on how to solve this permission issue ?

Answer 1

I think that you don't need to manually check if there is other instance running.

Sample job's step:

SELECT 1;
WAITFOR DELAY '00:00:45';

And try to run it twice:

EXEC msdb.dbo.sp_start_job 'job'
-- Job 'job' started successfully.

EXEC msdb.dbo.sp_start_job 'job'

Msg 22022, Level 16, State 1, Line 3

SQLServerAgent Error: Request to run job Job () refused because the job is already running from a request by <>.