Tracking DDL changes in SQL Server – the ‘Trouble with Triggers’

Tracking changes is an essential SQL Server security task. Besides data change history, which includes DML operations (e.g. INSERT, UPDATE, and DELETE), tracking DDL changes in SQL Server, changes that affect database objects, is of high importance too. In this regard, various techniques can be used as a schema change auditing solution. One of the most common method are DDL Triggers

SQL Server DDL triggers can be used in order to track DDL changes, whether the changes were intentional or accidental. Who committed the change, when, and what was affected by the committed action is the basic information needed to be captured using triggers. To use this solution, it’s required to create and maintain both triggers and captured information storage. Let’s see how tracking DDL changes in SQL Server via triggers works

Tracking DDL changes using triggers

A trigger is a special type of a database object which is automatically executed upon certain conditions – e.g. actions performed by the user. DDL triggers are used to audit CREATE, ALTER, DROP, and several other operations which perform DDL operations (GRANT, DENY, REVOKE or UPDATE STATISTICS)

In the following example, information about the event that fired the trigger is captured using the SQL Server’s EVENTDATA() function. The SQL script creates DDL trigger that captures CREATE, ALTER, and DROP events on a database level (although, triggers can be created on the server level to capture events for all databases on the server; ON ALL SERVER option should be used, instead of ON DATABASE):

CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO Audit_DDL_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

An appropriate storage table for the auditing data from EVENTDATA XML must be created also:

CREATE TABLE Audit_DDL_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);

Now, that we’ve set the stage for tracking DDL changes, the information in case of creating, modifying, or deleting a table captured via triggers would look like:

Although we’ve shown that this method can produce viable auditing results, tracking DDL changes in SQL Server using triggers has several disadvantages. First of all, a user with sufficient permissions can easily disable the triggers. Another way to avoid tracking DDL changes in SQL Server by triggers is to perform a schema change and then delete captured information from auditing repository. In our case, simply by deleting an appropriate entry from the Audit_DDL_Events table. Moreover, if no privileged users who can maliciously perform DDL changes exists, this method cannot provide information about changes that took place before it was set up

Tracking DDL changes using ApexSQL Log

ApexSQL Log is an auditing and recovery tool for SQL Server databases which audits, reverts or replays data and schema changes that have affected a database. The audited information may be provided even for the operations that were executed prior to installation of ApexSQL Log, as it reads database transaction log and transaction log backups which already contains records describing changes made to a database

To track DDL changes in SQL Server using ApexSQL Log:

1. Connect to the database

   

Add transaction log backups and/or detached transaction logs containing the data required to create the full chain

2. and provide the full database backup which will be used as a starting point from which the full chain of transactions starts

   

3. Opt to open results in grid

   

4. Use the Filter setup and the Time range section to specify the target point in time for the auditing process (the time frame you want to audit for DLL operations). This will narrow down the search and speed up the reading process

   

5. Finally, use the Operations filter to narrow down the search to the DDL statements only. To do this, deselect all DML and select desired (or all) DDL operations



6. When everything has been set, click Finish

When the process has finished, the main grid will show the transactions that can be rolled back, in order to revert the DDL changes, or exported for later analysis

You can export audited information as: CSV, HTML, SQL script, SQL bulk, and XML. To do so, use the Export option on the main toolbar, and select the desired output format. Both the SQL script and the SQL bulk export options populate repository SQL tables with captured data, providing the source for your own customized auditing reports

Finally, the process of tracking DDL changes in SQL Server using ApexSQL Log can be automated using CLI as explained in the Automating daily transaction log reading online article

Conclusion

As described, both DDL triggers and ApexSQL Log capture schema changes with the same relevant information. But unlike the DDL triggers, ApexSQL Log offers a simple point-and-click auditing technique, which doesn’t call for initial knowledge of SQL scripting. It’s capable of reading the information stored in both online and transaction log backups, that enables tracking DDL changes in SQL Server for the operations executed prior to the installation of the tool

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.