Auditing DDL Statements for user databases

Posted: September 24, 2009 in Sqlserver

Auditing DDL Statements for user databases


By creating the below trigger we can keep a track of changes made to any of the tables and its objects by users who is having dbo_access role on databases.

Requirements

·         SQL Server 2005 sqlserver with SP2.

·         Need to create DBA_Admin database on all the production sqlserver to create the EvtLog table which can be used to store the data of all the changes made to any of the sql user databases.

·         Need to create a trigger on all the production database.

 

 

Create the event log table

USE [DBA_Audit]

GO

/****** Object:  Table [dbo].[tblEventLog]    Script Date: 09/17/2009 14:41:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblEventLog](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [EventTime] [datetime] NULL,

      [EventType] [varchar](15) NULL,

      [ServerName] [varchar](25) NULL,

      [DatabaseName] [varchar](25) NULL,

      [ObjectType] [varchar](25) NULL,

      [ObjectName] [varchar](25) NULL,

      [UserName] [varchar](15) NULL,

      [CommandText] [varchar](max) NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

 

 

 

 

 

Create the DDL trigger

CREATE TRIGGER [trg_DDLChange_LOG] ON DATABASE — Create Database DDL Trigger

 

FOR DDL_DATABASE_LEVEL_EVENTS–CREATE_TABLE — Trigger will raise when creating a Table

 

AS

 

SET NOCOUNT ON

 

DECLARE @xmlEventData XML

 

 

— Capture the event data that is created

 

SET @xmlEventData = eventdata()

 

 

— Insert information to a EventLog table

 

INSERT INTO INLTE6531.DBA_Audit.dbo.tblEventLog

(

EventTime,

EventType,

ServerName,

DatabaseName,

ObjectType,

ObjectName,

UserName,

CommandText

)

 

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),

 

'T', ' '),

 

CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

 

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),

 

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),

 

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

 

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

 

CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),

 

CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

 

GO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s