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
CREATE TABLE ddl_log (programname varchar(100), PostTime datetime, username varchar(100), Event nvarchar(100), TSQL nvarchar(2000));
--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
CREATE TRIGGER monitoring_ddl
SET ANSI_PADDING ON
DECLARE @data XML
SET @data = EVENTDATA()
(programname, PostTime, username, Event, TSQL)
@data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)') ) ;
-- Command for dropping the trigger from the database
Sometimes we get the below error while the DDL trigger is executed.
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,
http://msdn.microsoft.com/en-us/library/ms186406(SQL.90).aspxhttp://msdn.microsoft.com/en-us/library/ms187909(SQL.90).aspxSanjaya PadhiSE, Microsoft SQL ServerReviewed By Anurag Sharma, SQL Server Escalation Services
PingBack from http://paidsurveyshub.info/story.php?id=75081