If you are a SQL DB admin the odds are you have a bunch of SQL agent jobs that run throughout the day. If you haven’t yet, at some point you will want to audit the specifics of those jobs and when they are running. The below script will query up the schedule details of your SQL Agent jobs. The script will also suggest SQL to change the job owner and schedule owner of any of your jobs/schedules that are owned by user logins. If you’ve not been bit by that in the past it is important to note that if a user login owns a job and that login is removed or denied access to connect to the database engine the job will cease to run correctly. Thus, I recommend setting the owner for all your jobs to “sa” to make sure that your jobs continue to run in perpetuity.
use msdb select J.job_id, J.name 'job_name', case when J.notify_level_email = '0' then 'Never' when J.notify_level_email = '1' then 'When the job succeeds' when J.notify_level_email = '2' then 'When the job fails' when J.notify_level_email = '3' then 'Whenever the job completes (regardless of the job outcome)' else cast(J.notify_level_email as varchar) end notify_level_email, O.name 'notify_name', O.email_address 'notify_email_address', SUSER_SNAME(J.owner_sid) 'job_owner', case when SUSER_SNAME(J.owner_sid) <> 'sa' and SUSER_SNAME(J.owner_sid) not like '##%' then 'exec dbo.sp_update_job @job_id=''' + cast(J.job_id as varchar(255)) + ''', @owner_login_name = ''sa''' end as job_owner_fix, J.enabled 'job_enabled', S.schedule_id, S.name 'schedule_name', S.enabled 'schedule_enabled', SUSER_SNAME(S.owner_sid) 'schedule_owner', case when SUSER_SNAME(S.owner_sid) <> 'sa' and SUSER_SNAME(S.owner_sid) not like '##%' then 'exec dbo.sp_update_schedule @schedule_id=' + cast(S.schedule_id as varchar) + ', @owner_login_name = ''sa''' end as schedule_owner_fix, case when S.freq_type = 1 then 'Once' when S.freq_type = 4 then 'Daily' when S.freq_type = 8 then 'Weekly' when S.freq_type = 16 then 'Monthly' when S.freq_type = 32 then 'Monthly relative' when S.freq_type = 64 then 'When SQLServer Agent starts' end frequency, left(convert(varchar, dbo.agent_datetime('19000101', S.active_start_time), 114), 8) start_time, case when S.freq_subday_interval = 0 then null else left(convert(varchar, dbo.agent_datetime('19000101', S.active_end_time), 114), 8) end end_time, case when S.freq_subday_interval = 0 then 'Once' else cast('Every ' + right(S.freq_subday_interval, 2) + ' ' + case when S.freq_subday_type = 1 then 'Once' when S.freq_subday_type = 4 then 'Minutes' when S.freq_subday_type = 8 then 'Hours' end as char(16)) end as 'sub_frequency', case when S.freq_type = 8 and S.freq_interval & 1 = 1 then 'x' else null end sunday, case when S.freq_type = 8 and S.freq_interval & 2 = 2 then 'x' else null end monday, case when S.freq_type = 8 and S.freq_interval & 4 = 4 then 'x' else null end tuesday, case when S.freq_type = 8 and S.freq_interval & 8 = 8 then 'x' else null end wednesday, case when S.freq_type = 8 and S.freq_interval & 16 = 16 then 'x' else null end thursday, case when S.freq_type = 8 and S.freq_interval & 32 = 32 then 'x' else null end friday, case when S.freq_type = 8 and S.freq_interval & 64 = 64 then 'x' else null end saturday, convert(varchar, dbo.agent_datetime(S.active_start_date, '000000000'), 101) duration_start, case when S.active_end_date = '99991231' then null else convert(varchar, dbo.agent_datetime(S.active_end_date, '000000000'), 101) end duration_end from msdb.dbo.sysjobs J left outer join msdb.dbo.sysoperators O on J.notify_email_operator_id = O.id left outer join msdb.dbo.sysjobschedules JS on J.job_id = JS.job_id left outer join msdb.dbo.sysschedules S on JS.schedule_id = S.schedule_id order by J.name