Monday, June 2, 2014

SQL Server Database Auditing Trigger

--SETTINGS WHEN CREATING THE TRIGGER
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'SchemaAuditDBDDLTrigger')
DISABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'SchemaAuditDBDDLTrigger')
DROP TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO

USE MyAuditDatabase
GO
/****** Object:  Table [SchemaAuditDB]    Script Date: 03/24/2014 23:40:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SchemaAuditDB') AND type in (N'U'))
drop taBLE [SchemaAuditDB]
GO

CREATE TABLE [SchemaAuditDB](
[SchemaAuditDBID] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[LoginName] [sysname] NOT NULL,
[UserName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [varchar](max) NOT NULL,
[XMLEventData] [xml] NOT NULL,
 CONSTRAINT [PK_SchemaAuditDB] PRIMARY KEY CLUSTERED
(
[SchemaAuditDBID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
USE MyDatabase
go
create TRIGGER [SchemaAuditDBDDLTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN

  SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
  SET NUMERIC_ROUNDABORT OFF

declare @ContextInfo varbinary(128)
select @ContextInfo = context_info from master.dbo.sysprocesses where spid=@@SPID;

--check for recursive execution  of trigger
IF @ContextInfo = 0x1
RETURN

  DECLARE
    @EventData XML,
    @Schema sysname,
    @Object sysname,
    @EventType sysname,
    @SQL VARCHAR(max)
 
  SET @EventData = EventData()

  SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
  SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
  SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')

  INSERT MyAuditDatabase.DBO.SchemaAuditDB (AuditDate, LoginName, UserName, [Event], [Schema], Object, TSQL, [XMLEventData])
  SELECT
    GetDate(),
    SUSER_SNAME(),
    @EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
    @EventType, @Schema, @Object,
    @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)'),
    @EventData
 
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

--DISABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
--GO

ENABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO


No comments:

Post a Comment