Did you know... Auditing using DDL Trigger in SQL Server 2005

  • Article

In the daily life of an SQL server DBA, we get numerous questions regarding the auditing possibilities for the different actions being performed on the database by different users. So it always comes handy if we have some kind of code ready to start the auditing of those specific events which are required. Also sometimes when we need to monitor the database to find the application which does the schema modifications to the objects.

 

Almost all the DBAs have some kind of code developed by them for the auditing of the required events in their environment. After all it makes life a lot easier. So find below a code snippet to enable the DDL auditing on the database using very useful Eventdata function.

 

DDL Auditing Code Snippet

===================

--Creation of the Audit table

--This code creates an trigger to audit the alter table events.

--We can create trigger for auditing all kind of the DDL events in similar format

GO

CREATE TABLE ddl_log (programname varchar(100), PostTime datetime, username varchar(100), Event nvarchar(100), TSQL nvarchar(2000));

GO

 

--Creation of the trigger to audit the data on the specific database. This collects data

--for the Alter database events only.

 

IF EXISTS (SELECT * FROM sys.triggers

    WHERE parent_class = 0 AND name = 'monitoring_ddl')

DROP TRIGGER monitoring_ddl

ON DATABASE;

GO

 

CREATE TRIGGER monitoring_ddl

ON DATABASE

FOR alter_table

AS

SET ANSI_PADDING ON

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

   (programname, PostTime, username, Event, TSQL)

   VALUES

   (program_name(), GETDATE(),

   suser_sname(),

   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

 

-- Command for dropping the trigger from the database

GO

DROP TRIGGER monitoring_ddl

ON DATABASE;

GO

 

Sometimes we get the below error while the DDL trigger is executed.

 

Error Message:

INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

 

This error message is quite descriptive. We are using the XML datatype and hence we will need to keep the ANSI_PADDING setting to "ON". So for this reason, we have added the SET ANSI_PADDING option explicitly in the trigger.

 

For more information on the DDL trigger and the Eventdata function, you can go through the below links,

https://msdn.microsoft.com/en-us/library/ms186406(SQL.90).aspx
https://msdn.microsoft.com/en-us/library/ms187909(SQL.90).aspx

Sanjaya Padhi
SE, Microsoft SQL Server
Reviewed By Anurag Sharma, SQL Server Escalation Services