Using DDL Triggers in SQL Server to audit database objects
Why do we need DDL triggers?
In your workplace does more than one person has access to databases that are vital to the smooth operation of your organisation? Do these users have rights to alter the database structure by using Data Definition Language (DDL) statements such as CREATE TABLE..., DROP TABLE etc?
If the answer to the above questions are yes then are mechanisms in place to help monitor/audit who is making what changes to the database? Who deleted that table? Who changed that columns data type? What code was in the previous version of that procedure that now isn't working? This type of auditing is being asked for more and more as organisations become more and more dependent on their databases for helping in all aspects of day to day work.
DDL Triggers (introduced in SQL Server 2005) provide you with the capability of auditing the creation, deletion and modification of database objects as well as other capabilities such as checking that DDL code conforms to your business rules before executing it.
How Triggers work
A Trigger is a block of T-SQL code that is executed or 'triggered' as a result of another statement that is sent to the database. Before SQL Server 2005 a trigger could be 'triggered' by either INSERT, UPDATE or DELETE (Data Manipulation Language - DML) statements. SQL Server 2005 introduced DML Triggers that can be set to fire on your chosen DDL events such as CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_DATABASE, CREATE_LOGIN etc.
DDL Triggers can be set with either a Server scope or database scope. Triggers created with Server scope must target server DDL events such as CREATE_DATABASE or CREATE_LOGIN whilst triggers created with database scope must target database level events such as CREATE_TABLE or ALTER_PROC. See the full list of SQL Server DDL Trigger Events (including their scope).
DDL triggers can only fire after the DDL statement has occurred. This is different from DML triggers which can fire before the triggering statement.
Syntax of a DDL trigger
CREATE TRIGGER [TriggerName] ON [Scope (Server|Database)] FOR [EventName...], AS -- code for your trigger response here
The EventData function
If you want to audit changes to your database schemas you need to be able to access the triggering events in your DDL trigger so that you can record what changes are being made. To access the triggering event we can use the EventData function in our DDL trigger. The EventData function returns an xml value.
The EventData xml value includes the triggering SQL statement, the event time, the type of event and depending on what type of event was called, extra information such as the database name. The following example shows how EventData can be used to record all statements that changed the table or stored proc schemas into a table called DDLAudit.
CREATE TRIGGER AuditProcChanges ON DATABASE FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS DECLARE @ed XML SET @ed = EVENTDATA() INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login) VALUES ( GetDate(), @ed.value('(/EVENT_INSTANCE/DatabaseName)', 'varchar(256)'), @ed.value('(/EVENT_INSTANCE/EventType)', nvarchar(100)'), @ed.value('(/EVENT_INSTANCE/ObjectName)', 'varchar(256)'), @ed.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'), @ed.value('(/EVENT_INSTANCE/LoginName)', 'varchar(256)') )
The EventData function returns an xml value and is assigned to a variable called @ed which is of an xml data type. The xquery function value(Xquery, sqltype) returns the specified values from the xml variable. For more information on EventData see MSDN.
This DDLAudit table could reside in the individual database or you could create a seperate ApplicationAudit database and use a 3 part name to record the audit in this ApplicationAudit database, i.e. INSERT INTO ApplicationAudit.dbo.DDLAudit ....
Covering all databases and events
If you wanted to audit the DDL events for all your databases you would need to create this trigger in each database. The above trigger only monitors DDL events that affect Tables and Stored Procedures. You can use the handy event name of DDL_DATABASE_LEVEL_EVENTS to make sure your trigger covers all DDL events that have database scope as follows:
CREATE TRIGGER AuditDBScopeDDLChanges ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS -- trigger code here...
Preventing DDL actions using triggers
Sometimes you may want to prevent the alteration of a schema, because the triggering statement and trigger are joined in one transaction we can call ROLLBACK in our trigger to rollback the DDL statement that caused the trigger to fire:
CREATE TRIGGER PreventDropTable ON DATABASE FOR DROP_TABLE AS PRINT 'Tables cannot be dropped' ROLLBACK
What happens if you then want to drop a table in the database with the above trigger? You can disable the trigger, drop the table and then re-enable the trigger:
DISABLE TRIGGER PreventDropTable ON DATABASE GO DROP TABLE MyTable GO ENABLE TRIGGER PreventDropTable ON DATABASE GO
You could also if you wished extend use this method of schema change prevention for server scope events to prevent the dropping of databases:
CREATE TRIGGER PreventDropDatabaseServerWide ON ALL SERVER FOR DROP_DATABASE AS PRINT 'Cannot drop tables, DDL Trigger will rollback' ROLLBACK
Finding DDL triggers using system tables
To view the DDL triggers in your databases of database scope you can query the sys.triggers table. To view the DDL triggers with server scope you need to query the sys.server_triggers table in the master database.
When designing your DDL trigger you will probably be performing one of more of the following actions:
- Recording changes made to the database schema
- Stopping certain types of changes being made to the database schema
- Fire another action in the database in response to the schema change
We have seen how DDL triggers can be used to a) audit and b) control schema changes using the EventData function and the ROLLBACK command respectively.