Monitoring and Recording DDL changes in SQL SERVER 2005

• Download ddldatabasetriggerscript - 1.38 KB

Introduction

While executing the IT project of any significant size, auditing any structural change during the course of project is important. Project manager handling the team size varying from 5 developer to 50 developer cannot do all the activities. Another challenge is that client does not agree to hire a dedicated DBA in case of project having team size less than 10 assuming there is no major activity to be performed. So that case developer would be acting as front end developer, middleware operation and also performing database operation.

Background

SQL Server 2005 has extended the trigger functionality you normally use with data manipulation language DML, command such as insert, update and delete to incorporate Data Definition Language (DDL) commands like CREATE Database, Drop Table, Alter Table. The new technology comes with two flavors

a. Server-level triggers (respond to changes on server)

b. Database-level triggers (specific to a given database changes)

Defining the DDL Trigger

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)

CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option /> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option /> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier /> ::=
    assembly_name.class_name.method_name

When you define a DDL trigger, you must decide on the scope of your trigger. Scope determines whether the trigger executes at the database or the server level. Trigger scopping : In the following example, DDL trigger safety will fire whenever a DROP TABLE or ALTER TABLE event occurs in the database

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

The EVENTDATA() function returns XML data with information such as event time, System Process ID (SPID), and type of event firing the trigger. A DDL trigger uses the EVENTDATA() function to determine how it must respond. The following is a sample of XML data returned by the EVENTDATA() function:

Implementing Database scope DDL Trigger

The objective of implementing the database ddl trigger is to trap the changes. So the first objective is to create a table which can hold the data about changes happening the database with date and time.

Table structure would be as follows

CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
    [DatabaseLogID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Creating database level trigger to log the records in the above table

CREATE TRIGGER [ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;

GO

Points of Interest

You will get a table with all changes recorded and that can be used as vital audit information at any level of project.

a. If you create manually using the sql management studio it will work
b. If you run the script for database table alter, drop or create it will work
c. If you change the constraint it would log 
d. if you break indexes or create them it will log