Implementation and usage of DDL Trigger and its saga in SQL Server 2005

A new feature was introduced in SQL Server 2005 called DDL Trigger, which fires in response to a variety of Server scope or Database scope, Data Definition Language (DDL) events. These events can generally be fired by any DDL statements like Create, Alter, Drop, or DCL (Data Control Language) like Grant, Deny or Revoke.

DDL trigger can be created at two levels:•

Database level (like Create, Alter, Drop table, Procedure, Function or any database scoped objects)
• Server Level trigger (like Create, Alter, Drop Databases/Login/ Certificate )
Problem
In the earlier versions, tracking DDL activities on a database or on a server was not straight forward. Running profiler or trace was the only solution. Consider, you have a group of developers responsible for all the Development activities like Creation or Modification of Stored Procedures. Typically, this group is added to DB_DDLAdmin database fixed role. Here, to track which developer has modified a particular stored procedure was not possible, since all users have the DDLAdmin privilege. To resolve these type of issues DDL trigger is introduced in SQL Server 2005.

DDL Triggers is useful for the following purpose:• Auditing
• Regulating database or server operation
• Securing the database or server from malpractice
• Issue an alert when a DDL event occurs in Database or Server

Permission
To create a DDL trigger with a server scope (ON ALL SERVER) requires CONTROL SERVER permissions on the server. To create a DDL trigger within the database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

DDL Event Group

The Figure 1 lists the DDL event groups that can be used to run a DDL trigger or an event notification and also the Transact-SQL statements they cover. You can use a top level Event group instead of mentioning all the events in the trigger. For example, using DDL_TABLE_VIEW_EVENTS event group instead of mentioning specified events like Create_Table,Alter_Table,Drop_Table,Create_view, Alter_View,Drop_View

Figure 1





How DDL Trigger works?

EventData()
EVENTDATA function returns XML data which contains the information about server or database events. EVENTDATA is called when an event notification fires and it can also be used inside the body of a DDL or logon trigger.

Database scoped DDL Trigger.

Any DDL statement, which alters the schema of an object inside the database, will be tracked in this type of trigger. (Please note that few events are not tracked (read as bug) and this will be fixed in SQL Server 2008 version). Generally, the DDL Statement at the database level are Create, Alter, Drop tables , Procedure ,Views ,Functions , Users, Role , Certificate, etc. (For all the database level events , please refer to http://msdn.microsoft.com/en-us/library/ms189871.aspx ). Either you can hard code the specific event to be tracked inside the trigger (like Create_Table,Create_Procedure etc) or use DDL_DATABASE_LEVEL_EVENTS event group to track all the database scoped events. Whether you need to track all the events or only specific events will depend upon the requirements. Also there are event groups like DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS which are also used in EventNotification. Mostly all the events are applicable for DDL Triggers as well.

Step by step in Brief

(a) Create a table to store the logged data. Here in this scenario the table created is DDLDatabaseScopedAuditTrail.
(b) Create DDL Trigger which logs the information into this table. Which event needs to be tracked will depend upon the requirements. EventData() function returns a whole lot of information. In this example, let us track Eventtype,Login,Database User name and TSQL which caused the trigger to fire and the fired Datetime. For a better understanding the output of EventData() function to an XML type column has been captured However, this may not be needed in the actual senario. Select only the necessary events as logging any extra information is always a performance hinderance.
This scenario, dipicts tacking three kind of events:
• Trigger to track all events .
• Trigger to track a group of events
• Trigger to track a specific event

Code Block for Database scoped DDL Trigger.

-- Database for DDL Trigger Demo
Create Database DDLTriggerTest
Go

-- Create Log table

If Object_ID('DDLAuditTrail') is not null
BEGIN
DROP TABLE DDLAuditTrail
END

CREATE TABLE dbo.DDLDatabaseScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)
Go

--Trigger to track all Database Events using EventGroup

Create TRIGGER db_DDLtrg_Audit_All_Database_Scoped_Events
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()
INSERT dbo. DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track specific group of Database events using Database Event Group

Create TRIGGER db_DDLtrg_Audit_All_TABLE_VIEW_EVENTS
ON DATABASE
FOR
DDL_TABLE_VIEW_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track very specific Database events


Create TRIGGER db_DDLtrg_Audit_Specific_Event
ON DATABASE
FOR
CREATE_Table, Create_Procedure
AS
SET NOCOUNT ON
If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

DDL Trigger for Server Scoped Objects Event tracking

Server scoped events can be tracked using a server scoped DDL trigger. Here also , you can track all the events using DDL_SERVER_LEVEL_EVENTS group event or you can be more specific tracking only CREATE_DATABASE and CREATE_LOGIN.

Steps in Breif :-(a) Create a table to store the logged data. Here the table created is DDLServerScopedAuditTrail. It is better to create this table in the Master or in any specific user database other than the business related database..
(b) Create DDL Trigger which logs the information into this table. Events depend upon the requirements. Here three types of events have been detailed.


DDL Trigger to track Server Scoped events


Use Master
GO
If Object_ID('DDLServerScopedAuditTrail') is not null
BEGIN
DROP TABLE DDLServerScopedAuditTrail
END

CREATE TABLE dbo.DDLServerScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)

Go

--Trigger to track all Server Events using EventGroup


CREATE TRIGGER Svr_DDLtrg_Audit_All_Server_events
ON All Server
FOR
DDL_SERVER_LEVEL_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)


End
Go


Trigger to track specific group of Server events using Server Event Group

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SECURITY_EVENTS
ON All Server
FOR
DDL_SERVER_SECURITY_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
End
GO


-- Trigger to track very specific Server events

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SPECIFIC_EVENT
ON All Server
FOR
CREATE_DATABASE ,CREATE_LOGIN
AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)

End


Limitation

• DDL triggers will not fire in response to events that affect local or global temporary tables and stored procedures.
• DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers
Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked

For more information, please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493
For an issue reported when you alter a Replicated Table, Please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331367

SQL Server 2008
In SQL Server 2008, there are few more events added like sp_Rename is tracked using RENAME Event.

System objects for DDL Triggers

You can use the sys.triggers catalog view to see the database scoped DDL triggers. The parent_class column in this catalog view has a value of Zero which indicates that it’s a DDL Trigger. You can use the sys.server_triggers catalog view to get a list of server scoped DDL triggers. The sys.trigger_events and sys.server_trigger_events catalog views contain a row for each time a database is scoped and a server scoped trigger is fired, respectively.

Query to find out the Events and other details of DDL Trigger

SELECT Name, s.type_desc SQL_or_CLR,is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON s.object_id = e.object_id

Where can I find DDL trigger in SSMO?

Database level DDL triggers are listed in the Programmability -- >> Database Triggers folder in Object Explorer. The server-level triggers are listed under the Server Objects -- >>Triggers folder in Object Explorer.

Figure 2



Drop DDL Trigger

The ON DATABASE or ON ALL SERVER clause should be used with DROP TRIGGER to drop a DDL trigger. All the other operations are similar to the DML trigger ie. ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger and ENABLE TRIGGER to enable a DDL trigger.

Summary
DDL Trigger without any doubt is one of the best features of SQL Server 2005. There are known issues like few commands are not tracked by any events. In spite of these limitations, it gives a very good value add to the product in day to day activities. Now-a-days, auditing is obvious in any application and DDL trigger fits in there. We can make a complete automated auditing system using the DDL trigger and Reporting Service of SQL Server 2005.