How to see job owner, Email operator, Event Notification, Email Notification on jobs scheduled?

Using the SELECT statement below one can list notications that occur for jobs scheduled on SQLServer.

SELECT –Job name
sj.name As JobName,
–Job owner
SUSER_SNAME(sj.owner_sid) As owner,
–Job category e.g. “Database Maintenance”, “Report Server”, “[Uncategorized (Local)]”
c.name as Category,
–Operator to be e-mailed (may be NULL)
o.name as EmailOperator,
–notification in Event log (notify when job fails, succeeds, always, never)
CASE sj.notify_level_eventlog
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END As EventLogNotification,
–e-mail notification (notify when job fails, succeeds, always, never)
CASE sj.notify_level_email
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END AS EmailNotification
FROM msdb.dbo.sysjobs sj
–E-Mail Operator
LEFT OUTER JOIN msdb.dbo.sysoperators o ON sj.notify_email_operator_id = o.id
–Job categories
LEFT OUTER JOIN msdb.dbo.syscategories C ON sj.category_id = c.category_id
WHERE –ignore auto-created jobs (Reporting Services schedules) uncomment if you want ignore
— NOT (sj.name LIKE '_____________-____-____-____________') AND
–only enabled Jobs
sj.enabled = 1
ORDER BY o.name, sj.name;

Sample Output:
JobName,Owner,Category,EmailOperator,EventLogNotification,EmailNotification
Test,sa,[Uncategorized (Local)],NULL,When the job fails,Never

One comment

Leave a Reply