Wednesday, April 18, 2012

how to create an audit trigger for sql agent jobs

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.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[sysjobs_a](
[job_id] [uniqueidentifier] NOT NULL,
[originating_server_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[start_step_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[owner_sid] [varbinary](85) NOT NULL,
[notify_level_eventlog] [int] NOT NULL,
[notify_level_email] [int] NOT NULL,
[notify_level_netsend] [int] NOT NULL,
[notify_level_page] [int] NOT NULL,
[notify_email_operator_id] [int] NOT NULL,
[notify_netsend_operator_id] [int] NOT NULL,
[notify_page_operator_id] [int] NOT NULL,
[delete_level] [int] NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[version_number] [int] NOT NULL,
[sz30auditusername] [varchar](30) NULL,
[sz1auditType] [char](1) NULL,
[sz30audithostname] [char](30) NULL,
[dtaudittimestamp] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Auditlist](
[sz30tablename] [varchar](128) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into auditlist values ('sysjobs')
go


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 deleted
go
--Warning this table has no update trigger - Printing please run
Create 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 inserted
go
--Warning this table has no insert trigger - Printing please run
Create 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 inserted
go

No comments:

Post a Comment