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?