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
Buyer Beware
There are a couple of things to consider before implementing this in a production SQL Server:
- 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.
-
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.
-
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE master; GO IF DB_ID(N'ddl_audit') IS NULL BEGIN CREATE DATABASE ddl_audit; ALTER DATABASE ddl_audit MODIFY FILE (NAME = N'ddl_audit', SIZE = 1000MB, MAXSIZE = 16000MB, FILEGROWTH = 1000MB) ALTER DATABASE ddl_audit MODIFY FILE (NAME = N'ddl_audit_log' , SIZE = 1000MB, MAXSIZE = 2000MB, FILEGROWTH = 500MB); ALTER DATABASE ddl_audit SET ENABLE_BROKER; ALTER DATABASE ddl_audit SET RECOVERY SIMPLE; BACKUP DATABASE ddl_audit TO DISK = N'NUL:'; END ALTER DATABASE ddl_audit SET AUTO_CREATE_STATISTICS ON; ALTER DATABASE ddl_audit SET AUTO_UPDATE_STATISTICS ON; ALTER DATABASE ddl_audit SET AUTO_UPDATE_STATISTICS_ASYNC ON; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
USE ddl_audit; GO IF OBJECT_ID(N'dbo.ddl_events_raw', N'U') IS NOT NULL BEGIN DROP TABLE dbo.ddl_events_raw; END CREATE TABLE dbo.ddl_events_raw ( [status] tinyint NOT NULL , [priority] tinyint NOT NULL , [queuing_order] bigint NOT NULL , [conversation_group_id] uniqueidentifier NOT NULL , [conversation_handle] uniqueidentifier NOT NULL , [message_sequence_number] bigint NOT NULL , [service_name] nvarchar(512) NOT NULL , [service_id] int NOT NULL , [service_contract_name] nvarchar(256) NOT NULL , [service_contract_id] int NOT NULL , [message_type_name] nvarchar(256) NOT NULL , [message_type_id] int NOT NULL , [validation] nchar(2) NOT NULL --E (Empty), N (None), X (XML) , [message_body] varbinary(max) ); IF OBJECT_ID(N'dbo.ddl_events_log', N'U') IS NOT NULL BEGIN DROP TABLE dbo.ddl_events_log; END CREATE TABLE dbo.ddl_events_log ( ddl_events_log_id int NOT NULL CONSTRAINT ddl_events_log_pk PRIMARY KEY CLUSTERED WITH ( DATA_COMPRESSION = PAGE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 100 , PAD_INDEX = OFF ) IDENTITY(1,1) , [EventType] sysname NOT NULL , [PostTime] datetime NOT NULL , [SPID] int NULL , [ServerName] sysname NULL , [LoginName] sysname NULL , [UserName] sysname NULL , [DatabaseName] sysname NULL , [SchemaName] sysname NULL , [ObjectName] sysname NULL , [ObjectType] sysname NULL , SetOptionsAnsiNulls nvarchar(3) NULL , SetOptionsAnsiNullDefault nvarchar(3) NULL , SetOptionsAnsiPadding nvarchar(3) NULL , SetOptionsQuotedIdentifier nvarchar(3) NULL , SetOptionsEncrypted nvarchar(5) NULL , [TSQLCommand] nvarchar(max) NOT NULL ) ON [DEFAULT]; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
IF OBJECT_ID(N'dbo.capture_server_wide_ddl_events', N'P') IS NOT NULL BEGIN DROP PROCEDURE dbo.capture_server_wide_ddl_events; END GO CREATE PROCEDURE dbo.capture_server_wide_ddl_events AS BEGIN DECLARE @q TABLE ( [status] tinyint NOT NULL , [priority] tinyint NOT NULL , [queuing_order] bigint NOT NULL , [conversation_group_id] uniqueidentifier NOT NULL , [conversation_handle] uniqueidentifier NOT NULL , [message_sequence_number] bigint NOT NULL , [service_name] nvarchar(512) NOT NULL , [service_id] int NOT NULL , [service_contract_name] nvarchar(256) NOT NULL , [service_contract_id] int NOT NULL , [message_type_name] nvarchar(256) NOT NULL , [message_type_id] int NOT NULL , [validation] nchar(2) NOT NULL --E (Empty), N (None), X (XML) , [message_body] varbinary(max) ); WAITFOR ( RECEIVE TOP (1) [status] , [priority] , [queuing_order] , [conversation_group_id] , [conversation_handle] , [message_sequence_number] , [service_name] , [service_id] , [service_contract_name] , [service_contract_id] , [message_type_name] , [message_type_id] , [validation] , [message_body] FROM dbo.server_wide_ddl_queue INTO @q ); --you can remove this INSERT INTO statement if you don't care about capturing the raw XML. It's useful for debugging, but that's about it. INSERT INTO dbo.ddl_events_raw SELECT * FROM @q q WHERE q.[validation] = N'X'; DECLARE @xml xml = NULL; SELECT @xml = CASE WHEN lar.[validation] = N'X' THEN CONVERT(xml, lar.message_body) ELSE NULL END FROM @q lar; IF @xml IS NOT NULL BEGIN DECLARE @EventType sysname; DECLARE @PostTime datetime; DECLARE @SPID int; DECLARE @ServerName sysname; DECLARE @LoginName sysname; DECLARE @UserName sysname; DECLARE @DatabaseName sysname; DECLARE @SchemaName sysname; DECLARE @ObjectName sysname; DECLARE @ObjectType sysname; DECLARE @SetOptionsAnsiNulls nvarchar(3); DECLARE @SetOptionsAnsiNullDefault nvarchar(3); DECLARE @SetOptionsAnsiPadding nvarchar(3); DECLARE @SetOptionsQuotedIdentifier nvarchar(3); DECLARE @SetOptionsEncrypted nvarchar(5); DECLARE @TSQLCommand nvarchar(max); SELECT @EventType = @xml.value(N'(/EVENT_INSTANCE/EventType)[1]', N'sysname') , @PostTime = @xml.value(N'(/EVENT_INSTANCE/PostTime)[1]', N'datetime') , @SPID = @xml.value(N'(/EVENT_INSTANCE/SPID)[1]', N'int') , @ServerName = @xml.value(N'(/EVENT_INSTANCE/ServerName)[1]', N'sysname') , @LoginName = @xml.value(N'(/EVENT_INSTANCE/LoginName)[1]', N'sysname') , @UserName = @xml.value(N'(/EVENT_INSTANCE/UserName)[1]', N'sysname') , @DatabaseName = @xml.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname') , @SchemaName = @xml.value(N'(/EVENT_INSTANCE/SchemaName)[1]', N'sysname') , @ObjectName = @xml.value(N'(/EVENT_INSTANCE/ObjectName)[1]', N'sysname') , @ObjectType = @xml.value(N'(/EVENT_INSTANCE/ObjectType)[1]', N'sysname') , @SetOptionsAnsiNulls = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULLS)[1]', N'nvarchar(3)') , @SetOptionsAnsiNullDefault = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULL_DEFAULT)[1]', N'nvarchar(3)') , @SetOptionsAnsiPadding = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_PADDING)[1]', N'nvarchar(3)') , @SetOptionsQuotedIdentifier = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/SetOptions/@QUOTED_IDENTIFIER)[1]', N'nvarchar(3)') , @SetOptionsEncrypted = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/SetOptions/@ENCRYPTED)[1]', N'nvarchar(5)') , @TSQLCommand = @xml.value(N'(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', N'nvarchar(max)'); INSERT INTO dbo.ddl_events_log ( [EventType] , [PostTime] , [SPID] , [ServerName] , [LoginName] , [UserName] , [DatabaseName] , [SchemaName] , [ObjectName] , [ObjectType] , [SetOptionsAnsiNulls] , [SetOptionsAnsiNullDefault] , [SetOptionsAnsiPadding] , [SetOptionsQuotedIdentifier] , [SetOptionsEncrypted] , [TSQLCommand] ) VALUES ( @EventType , @PostTime , @SPID , @ServerName , @LoginName , @UserName , @DatabaseName , @SchemaName , @ObjectName , @ObjectType , @SetOptionsAnsiNulls , @SetOptionsAnsiNullDefault , @SetOptionsAnsiPadding , @SetOptionsQuotedIdentifier , @SetOptionsEncrypted , @TSQLCommand ); END END GO |
Next, we’ll create the Service Broker infrastructure and Event Notification required to tie this all together.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
IF EXISTS ( SELECT 1 FROM sys.event_notifications en WHERE en.name = N'server_wide_ddl_events' ) BEGIN DROP EVENT NOTIFICATION server_wide_ddl_events ON SERVER; END IF EXISTS ( SELECT 1 FROM sys.routes r WHERE r.name = N'server_wide_ddl_audit_route' ) BEGIN DROP ROUTE server_wide_ddl_audit_route; END IF EXISTS ( SELECT 1 FROM sys.services s WHERE s.name = N'server_wide_ddl_service' ) BEGIN DROP SERVICE server_wide_ddl_service; END IF EXISTS ( SELECT 1 FROM sys.service_queues sq INNER JOIN sys.schemas s ON sq.schema_id = s.schema_id WHERE s.name = N'dbo' AND sq.name = N'server_wide_ddl_queue' ) BEGIN DROP QUEUE dbo.server_wide_ddl_queue; END GO CREATE QUEUE dbo.server_wide_ddl_queue ON [DEFAULT]; ALTER QUEUE dbo.server_wide_ddl_queue WITH STATUS = OFF , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = ddl_audit.dbo.capture_server_wide_ddl_events , EXECUTE AS OWNER ); CREATE SERVICE server_wide_ddl_service AUTHORIZATION dbo ON QUEUE dbo.server_wide_ddl_queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); CREATE ROUTE server_wide_ddl_audit_route WITH SERVICE_NAME = N'server_wide_ddl_service' , ADDRESS = N'local'; CREATE EVENT NOTIFICATION server_wide_ddl_events ON SERVER FOR ddl_events TO SERVICE N'server_wide_ddl_service', N'current database'; |
To enable the queue, and actually capture DDL events, we need to run the following:
|
1 2 3 4 5 6 7 |
ALTER QUEUE dbo.server_wide_ddl_queue WITH ACTIVATION ( STATUS = ON , MAX_QUEUE_READERS = 2 ) , RETENTION = OFF , STATUS = ON; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE ddl_audit; DROP TABLE dbo.t; CREATE TABLE dbo.t (i int); SELECT del.PostTime , del.DatabaseName , del.LoginName , del.SchemaName , del.ObjectName , del.TSQLCommand FROM dbo.ddl_events_log del |
The output looks like this:
╔═════════════════════════╦═══════════╦═══════╦════════╦════════════════╦════════════════════════════════════╗ ║ Post Time ║ Database ║ Login ║ Schema ║ Object Name ║ TSQLCommand ║ ║ ║ Name ║ Name ║ Name ║ ║ 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.
Actually, SQL Server does have built-in DDL auditing capabilities. It is part of the SQL Server Audit feature. I know it is in 2012 and above, but not sure if it exists in prior versions. See https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15.
Server level audits are in both Standard and Enterprise editions; however, database level audits require Enterprise edition. The auditing of DDL statements require database level auditing.
As for the suggested solution in this blog post, I would add deny for update, insert, and delete for users to the ddl_events_log table to prevent tampering of the audit events.
All great points, Robert. I’ve used the built-in Audit facility, and it’s perhaps not the easiest thing to query those audit results. At least, my preference for dealing with SQL Server data is to use standard T-SQL, which my solution provides quite nicely.
Your point about securing the audit table is very valid; so, thanks!
[…] Hannah Vernon wants to audit Data Definition Language events: […]
This is a great article. I have my own set of scripts and was checking for yours. You setup:
FOR ddl_events,
… while I, for some reason, tried to itemize the DDL (The child events etc). BOL has a script to list the heirarchy of Events and I see
FOR ddl_events,
ALTER_SERVER_CONFIGURATION
That should cover everything, right ? Right now, I am using your scripts to test but it seems I am not getting any events. I’ll figure it out or write back.
Thanks
I’ve been using this on multiple servers, and do see rows in the target table. Did you end up finding some issue?
CREATE EVENT NOTIFICATION server_wide_ddl_events
ON SERVER
FOR ddl_events
TO SERVICE N’server_wide_ddl_service’, N’current database’;
But the cleanup you do before is:
IF EXISTS (
SELECT 1
FROM sys.event_notifications en
WHERE en.name = N’server_wide_ddl_events’
)
BEGIN
DROP EVENT NOTIFICATION server_wide_ddl_events
ON SERVER;
END
It needs to be on server events:
IF EXISTS (
SELECT 1
FROM sys.server_event_notifications en
WHERE en.name = N’server_wide_ddl_events’
)
BEGIN
DROP EVENT NOTIFICATION server_wide_ddl_events
ON SERVER;
END
Thanks, I’ll take a look later today and correct the script.
Everything runs fine now. I was rerunning the scripts not getting a clean cleanup due to event object still in master. Just proves that the heirarchy of the service broker objects need to be created/dropped in correct order. They were all there but it was not working. Probably some internal Id didn’t match.
I found the article to see if anyone did a comparison to Sql Audit technology vs Event Notification which is older technology. I agree with you that having the data at your fingertips trumps the more secure Audit. I think I will setup a very narrow audit on the database objects created by your scripts. If anyone tempers with the data or objects, that will be more secure.
Nice website. I will keep in touch