运行SqlAgent作业时的权限问题

Question

当我尝试运行Sql代理作业时，我正在检查是否没有先前的实例在同一时间运行。下面的查询给出：

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 

但是，我得到的是

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

该步骤失败的错误。作业步骤由具有sysadmin特权的帐户运行。

我试图在msdb数据库也给db_owner特权的帐户。仍然面临同样的问题。

你能指导我，关于如何解决这个权限问题？

Answer 1

我认为你不需要手动检查是否有其他实例在运行。

采样作业的步骤：

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

并尝试两次运行它：

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 <>.