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 thought on “How to see job owner, Email operator, Event Notification, Email Notification on jobs scheduled?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s