Thursday, May 17, 2018

job schedule owner

SELECT
JobName = job.name,
JobOwner = dbp.name,
ScheduleName = sch.Name,
ScheduleOwner = schdbp.name
from msdb.dbo.sysschedules sch
left join msdb.dbo.sysjobschedules AS jobsch ON sch.schedule_id = jobsch.schedule_id
left join msdb.dbo.sysjobs AS job ON jobsch.job_id = job.job_id
--left join msdb.dbo.sysjobsteps AS jobstp ON job.job_id = jobstp.job_id AND job.start_step_id = jobstp.step_id
left join msdb.sys.database_principals AS dbp ON job.owner_sid = dbp.sid
left join msdb.sys.database_principals AS schdbp ON sch.owner_sid = schdbp.sid
left join msdb.dbo.syscategories AS cat ON job.category_id = cat.category_id
where job.name in ('xxx')
--where sch.name like '%Monthly Reporting%'
order by job.name, sch.name

No comments: