Monitor database activities using DDL trigger in SQL Server

We can have two types of DDL Trigger based on their functional scope.
1. Database wide DDL Trigger (has Scope for particular designated database on server and not for all database on server) and
2. Server-wide DDL Trigger (has Scope for server specific DDL change within the current server)[i]

Unlike the insert, update or delete data modification statements used in DML Trigger, DDL trigger use event_type for firing the trigger. For example CREATE_TABLE event_type propagate on launch of create table DDL command. Complete list of DDL events for Database and Server is available at this link. The area of interest of this article is Database wide DDL triggers.

Implementation
Following is the syntax for the DDL Trigger.

CREATE TRIGGER trigger_name
ON { ALL SERVER DATABASE }
[ WITH ENCRYPTION ]
{ FOR AFTER } { event_type event_group } [ ,...n ]
AS { sql_statement }

Note: DDL have FOR and AFTER options only, INSTEAD OF option is not valid for DDL Triggers.

Let us take an example where we need to prevent any DDL action (alter, create, drop) on any table in a database and Log the DDL Action to an Audit table. So to do this task following are the list of proceedings.

1. An Audit table ( This may be present on any database on same server or other server)[ii]
2. DDL Trigger on Current database.
3. Any DDL actions on a table in the Current DB result in firing the DDL trigger created above and insert Log information to the Audit table.

Before going through the example, EVENTDATA () function needs some explanation.
EVENDATA (): This function is used to get the information about server or database events inside DDL Trigger. It returns information in form of XML Schema.

Following are the Steps to create DDL Trigger for scenario describe above:

Step1: Create Log table on server [ServerName1\instance1] for [DBA] Database

CREATE TABLE [ServerName1\instance1].[DBA].[dbo].[DBAAudit] (
DDLEventType NVARCHAR(200) NOT NULL,
UserLoginName NVARCHAR(50) NOT NULL,
UserName NVARCHAR(50) NOT NULL,
ServerName NVARCHAR(100) NOT NULL,
DatabaseName NVARCHAR(100) NOT NULL,
ObjectName NVARCHAR(100) NOT NULL,
ObjectType NVARCHAR(50) NOT NULL,
CommandText NVARCHAR(MAX) NOT NULL,
ActionDateTime DATETIME NOT NULL)

Step2: Create trigger on server [ServerName2\instance1] for AdventureWorks DB

USE [AdventureWorks]
GO
CREATE TRIGGER [trDBADDLAuditLog]
ON DATABASE WITH ENCRYPTION
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
BEGIN
SET NOCOUNT ON
DECLARE @eventData XML
SET @eventData = EVENTDATA()

ROLLBACK --If DDL Change not acceptable, otherwise remove it
Print'DDL chanage require DBA Approval.'

INSERT INTO [ServerName1\instance1].[DBA].[dbo].[DBAAudit]
(
DDLEventType,
UserLoginName,
UserName,
ServerName,
DatabaseName,
ObjectName,
ObjectType,
CommandText ,
ActionDateTime
)
SELECT
EventNode.value(N'EventType[1]',N'NVARCHAR(200)'),
EventNode.value(N'LoginName[1]',N'NVARCHAR(50)'),
EventNode.value(N'UserName[1]',N'NVARCHAR(50)'),
EventNode.value(N'ServerName[1]',N'NVARCHAR(100)'),
EventNode.value(N'DatabaseName[1]',N'NVARCHAR(100)'),
EventNode.value(N'ObjectName[1]',N'NVARCHAR(100)'),
EventNode.value(N'ObjectType[1]',N'NVARCHAR(50)'),
EventNode.value(N'(TSQLCommand/CommandText)[1]',N'NVARCHAR(max)'),
EventNode.value(N'PostTime[1]',N'DATETIME')
FROM
@eventData.nodes('/EVENT_INSTANCE') EventTable(EventNode);
END

Note: Trigger Name (trDBADDLAuditLog) can not be prefix with schema, databasename or server name. Example trigger name like [dbo].[ trDBADDLAuditLog] is incorrect.

Step3: Now try to create a new table or alter or drop existing table on server [ServerName2\instance1] for AdventureWorks DB, You will get error like shown below because trigger is aborting the user action. But it log user action on server [ServerName1\instance1]’s DBA Database’s DBAAudit table. Check the record of DBAAudit Table.

DDL chanage require DBA Approval.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

________________________________________________________________________________________________________________
[i] Both Database and Server level have different set of trigger events according to scope and can be found at link
[ii] Table on other database and server can be accessed using 4 part naming convention, and if server is different than the current server, then Link Server object should be set on current server pointing to referenced server.