Sql-server – Job Information – When querying [msdb].[dbo].SYSJOBS_VIEW through linked server

linked-serverquerysql server

I am trying to build a report to query all my sql server jobs and thier status from the previous night's run. In an effort to have just one report instead of a separate report for each server, I am using linked server connections to query each sql server from the report.

When I try to run the query through a linked server connection, I don't get the data for the column originating_server, in the [msdb].[dbo].SYSJOBS_VIEW, or any columns in this view. This is so I know where the job is being run.

When I run the query on a local server, it runs fine and gives me the originating_server as expected.

Is this a permissions issue? The user connecting has db_datareader on the msdb database on the linked server.

DECLARE @YEAR INT,@MONTH INT,@DAY INT,@DAY2 INT
DECLARE @DATEs VARCHAR(10),@DATES2 VARCHAR(10)
DECLARE @LENTGH VARCHAR(8)
SET @YEAR=YEAR(GETDATE())
SET @MONTH=MONTH(GETDATE())
SET @DAY = DAY(GETDATE())
SET @DAY2 = DAY(DATEADD(DAY,-1,GETDATE()))
SET @DATES =CONVERT(VARCHAR(10),GETDATE(),121)
SET @DATES2 =CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),121)



SELECT JOB.NAME
,JOBVIEW.ORIGINATING_SERVER AS SERVERNAME
,CASE
    WHEN JOB.ENABLED=1 THEN 'ACTIVE'
    WHEN JOB.ENABLED=0 THEN 'INACTIVE'
    END ACTIVE
,CASE
    WHEN LAST_RUN_OUTCOME=0 THEN 'FAIL'
    WHEN LAST_RUN_OUTCOME=1 THEN 'SUCCEED'
    WHEN LAST_RUN_OUTCOME=3 THEN 'CANCEL'
    ELSE 'UNKNOWN'
END STATUS
,LAST_OUTCOME_MESSAGE
,LAST_RUN_DATE
,NEXT_RUN_DATE
,LAST_RUN_DURATION
FROM [linkedserver].[msdb].[dbo].SYSJOBS JOB
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSERVERS JOBSERVER ON JOB.JOB_ID=JOBSERVER.JOB_ID 
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSCHEDULES JOBSCH ON JOB.JOB_ID = JOBSCH.JOB_ID
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBS_VIEW JOBVIEW ON JOB.JOB_ID = JOBVIEW.JOB_ID
WHERE LAST_RUN_DATE IN (REPLACE(@DATES,'-',''),REPLACE(@DATES2,'-',''))

When I give the user sysadmin, I get the information I need, but I don't really want this user to have sysadmin. Is there a specific permission I can grant to the users?

Best Answer

I tested your code by creating a linked server and following @Erik's comment grant the user 'view database state' and then 'view server state'. Did not work.

If you backtrack the view by using sp_helptext of SYSJOBS_VIEW and sysoriginatingservers_view it uses an undocumented object named sysoriginatingservers. It is not documented what permission is required to read from sysoriginatingservers.

Minimum permission I was able to get the SYSJOBS_VIEW.ORIGINATING_SERVER column value by adding the user as a member of these to database_role of MSDB database.

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'taiobtest';
EXEC sp_addrolemember @rolename = 'SQLAgentReaderRole', @membername = 'taiobtest';

Another workaround is to replace your code with below by which you can avoid making the user member of SQLAgentReaderRole role. 

UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  AS SERVERNAME
Related Question