2

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?

1

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  
0

Another way to do is to use dbatools Get-DbaAgentJobHistory

Gets execution history of SQL Agent Job on instance(s) of SQL Server.

e.g.

Get-DbaAgentJobHistory -SqlInstance localhost, sql2016

Returns all SQL Agent Job execution results for the local and sql2016 SQL Server instances.

You can do all sort of wild card filtering.

You can even pipe the output to a variable and have the results written to a central database table.

e.g.

$results = Get-DbaAgentJobHistory -SqlInstance sql2017, sql2016 

Above gets the results from 2 instances into a variable. Then you can use Write-DbaDbTableData1 to write to your central database. Below will auto-create a table called dbo.Jobhistory in the CentralDBname database on sql2019 server

Write-DbaDbTableData -InputObject $results -SqlInstance sql2019 -Table "[[CentralDbName]]].[dbo].[`"[jobHistory]]`"]" -AutoCreateTable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.