Wednesday, April 18, 2012

Tired of jobs being changed without your knowledge, here is a sample of a audit of the sysjobs view in MSSQL 2008 this will audit update, delete and insert and tell you the time and the hostname and user who changed the table. You can add more audits too.

Create trigger trsysjobsDelete on sysjobs for delete as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from deletedgo--Warning this table has no update trigger - Printing please runCreate trigger trsysjobsUpdate on sysjobs for update as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from insertedgo--Warning this table has no insert trigger - Printing please runCreate trigger trsysjobsInsert on sysjobs for insert as if exists(select sz30tablename from auditlist where sz30tablename='sysjobs') insert into sysjobs_a ([job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],[sz30auditusername],[sz1auditType],[sz30audithostname],[dtaudittimestamp]) select [job_id],[originating_server_id],[name],[enabled],[description],[start_step_id],[category_id],[owner_sid],[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator_id],[notify_netsend_operator_id],[notify_page_operator_id],[delete_level],[date_created],[date_modified],[version_number],user_name(),'D',host_name(),getdate() from insertedgo