DDL Trigger and DDL Auditing in SQL Server 2005 by serverku

Hope this help you. For very sensitive and secure databases, it is required to track all the activities and changes done with the database objects. And it should be very secure as well, so nobody can play with database objects. Even we should have all the history and tracking for what are database object structure changes occurred, what, when and who did it?

For the tracking all these stuffs we need to have a better process to change them and it should be done by a responsible person. Bur sometime the issues may be occur with database objects changes, at that time if we have all the enough information then we can correct and revert it back as original. Today I would like to introduce to handle all the stuffs, and it is Database DDL Triggers. DDL Triggers can be specified at database and server level, but for the database object tracking we need it on the database. Let us see an example.

-- Creating audit database
CREATE DATABASE AuditDatabase
GO

USE AuditDatabase
GO

-- Creating table, which capture all the objects structure changes
CREATE TABLE ObjectTracking
(
TrackingId bigint identity(1,1),
TrackingDate datetime NOT NULL DEFAULT GETDATE(),
DatabaseName varchar(500),
EventType varchar(500),
ObjectName varchar(500),
ObjectType varchar(500),
LoginName varchar(500),
HostName varchar(500),
SqlCommand nvarchar(max)
)

GO

Now to audit the database objects, DDL operation or structure changes we have to create a DDL trigger at database level.

-- Creating triggers, which will be fire on every objects actions on the database of that instances.
CREATE TRIGGER [AuditObjects]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS 
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
set @HostName = HOST_NAME()

-- Inserting tracking information in audit table
INSERT INTO ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
END
GO

Now we have created tables and DDL triggers to capture the object movement. Now we have turn to test it.
Checking :

-- Creating , Altering and Destroying some of the various objects
-- Creating table event
CREATE TABLE AuditTest (AuditId int, Auditname char(10))
GO

-- Altering table event
ALTER TABLE AuditTest
ADD  AuditDate datetime NULL
GO

-- Creating Indexes events
CREATE CLUSTERED INDEX  ix_AuditId on AuditTest(AuditId)
GO

CREATE NONCLUSTERED INDEX  ix_AuditDate on AuditTest(AuditDate)
GO

-- Dropping index events
DROP INDEX  ix_AuditDate on AuditTest
GO

-- Creating view events
CREATE VIEW AuditView
AS
SELECT * FROM AUDITTEST
GO

-- Creating procedure events
CREATE PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AUDITVIEW
END
GO

-- Altering procedure events
ALTER PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AuditTest
END
GO

-- Dropping view and table events
DROP VIEW AuditView
GO

DROP TABLE AuditTest
GO

Now audit test over, we have to review the history in audit table now,

SELECT
TrackingId,
TrackingDate,
DatabaseName,
EventType,
ObjectName,
ObjectType,
LoginName,
--HostName,
'Paresh-PC' as HostName,
SqlCommand
FROM ObjectTracking
ORDER BY TrackingDate
GO

Conclusion : We have some other new features for DDL Auditing in SQL Server 2008. But before SQL Server 2008 we can use this method for auditing.

Are you using this or using something else? Make your comments.