Auditing DDL Events

SQL Server doesn’t audit DDL events out of the box, aside from several major events which are captured in the Default System Trace. By “audit”, I mean there is no log of the data-definition-language statements issued against the server. So, if someone creates a table in a database, you can see the table, and when it was created, but details about who created it, and what code they used to create it isn’t saved anywhere. The code in this blog post shows how to configure SQL Server Event Notifications in tandem with SQL Server Service Broker to capture all SQL Server DDL events asynchronously to a database specially configured for the purpose.

Théodore Rousseau - Landscape with Figures

Théodore Rousseau – Landscape with Figures

Buyer Beware

There are a couple of things to consider before implementing this in a production SQL Server:

  1. Depending on the frequency of DDL statements, the log can get pretty large quickly. Most production servers don’t see all that many DDL statements; the majority are Data Manipulation Language (DML) statements which modify data, so this is not a commonly occuring problem – I just want to point out that you should monitor the size of the log table over time.

  2. SQL Server attempts to replace security-sensitive strings such as passwords with asterisks, but this replacement feature is not guaranteed to work for all possible scenarios. As such, you should ensure the database cannot be accessed by anyone other than members of the sysadmin role. Check my previous post on Login Auditing for a way to log all connection attempts.

  3. This will add CPU and I/O load to your server, albeit a typically very small amount. For a very busy server, this is probably not a great idea, unless you’re using this methodology as a way to troubleshoot something over a limited period of time.

The Setup

First off, we need a place to store the log, so we’ll create a dedicated database.

The code above will create the database in the default data and log file locations – you might want to modify the statement to be specific about what you call these files, and where they’ll be created. See Microsoft’s CREATE DATABASE docs for details. I’ve configured the database to enable Service Broker, since that is required for everything to work. The database is set to simple recovery model since I don’t typically need to recover this database to a specific point-in-time. If you have a directive to capture all DDL events, you should configure the recovery model accordingly, and ensure you take database and log backups that will enable you to meet your Recovery Time Objective and Recovery Point Objective.

I explicitly set the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS settings to “on” for the database, since some environments have this turned off by default. Having SQL Server automatically create the statistics it needs is normally a good idea.

Next, we’ll create a couple of tables; a “raw” table to capture the Event Notification XML directly from the queue, and a “log” table where we’ll store the parsed XML for easier querying.

Next we’ll create a stored procedure that will de-queue the Service Broker messages. The stored procedure will only ever dequeue one message at a time, as a result of the TOP(1) clause in the RECIEVE statement. The stored procedure will automatically be called by SQL Server Service Broker for each enqueued message.

Next, we’ll create the Service Broker infrastructure and Event Notification required to tie this all together.

To enable the queue, and actually capture DDL events, we need to run the following:

Check the Microsoft Docs for ALTER QUEUE for details about the parameters.

Once the queue is enabled, you’ll start seeing DDL statements being logged to the dbo.ddl_events_log table.

I use the following simple test:

The output looks like this:

╔═════════════════════════╦══════════════╦═══════════╦════════════╦════════════════╦════════════════════════════════════╗
║        PostTime         ║ DatabaseName ║ LoginName ║ SchemaName ║   ObjectName   ║            TSQLCommand             ║
╠═════════════════════════╬══════════════╬═══════════╬════════════╬════════════════╬════════════════════════════════════╣
║ 2020-02-05 11:35:41.433 ║ ddl_audit    ║ me        ║ dbo        ║ ddl_events_log ║ DROP TABLE dbo.ddl_events_log      ║
║ 2020-02-05 11:35:41.437 ║ ddl_audit    ║ me        ║ dbo        ║ ddl_events_log ║ CREATE TABLE dbo.ddl_events_log... ║
╚═════════════════════════╩══════════════╩═══════════╩════════════╩════════════════╩════════════════════════════════════╝

Let me know what you think of this method used to audit DDL events. If you find any issues or have questions about the code, let me know in the comments below!

Check out the rest of our posts on T-SQL.