By: Aaron Bertrand | Last Updated: 2018-08-27 | Comments | Database Administration
In a previous tip, SQL Server DDL Triggers to Track All Database Changes, and in a couple of follow-ups, I explained how to capture all DDL changes on a SQL Server instance and store them in an auditing table. I left it as an exercise to the reader to determine how (and how often) they would use that table to notify their team about changes that had taken place. Some people just added an e-mail call within the trigger, and others set up jobs that polled the table periodically. I wanted to post a follow-up tip to describe how I would do it, along with a couple of corrections to the way I set things up in those earlier tips.
Let’s get some housekeeping out of the way first. There were two important things I wanted to correct that were brought up by readers of the previous tips:
- If a DDL event occurs under snapshot isolation, and the auditing database
does not support snapshot isolation, you will get this error:
Msg 3952, Level 16, State 1, Procedure DDLTrigger_SampleTo avoid this error, you’ll want to issue this statement against your auditing database:
Snapshot isolation transaction failed accessing database 'AuditDB' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
ALTER DATABASE AuditDB SET ALLOW_SNAPSHOT_ISOLATION ON;
- If you implemented anything to provide regular users with additional permissions
to access the IP address of the user through sys.dm_exec_connections, such as
VIEW SERVER STATE or IMPERSONATE, you should change the following pieces of
DECLARE @ip VARCHAR(32) =To this:
WHERE session_id = @@SPID
DECLARE @ip varchar(48) = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address'));Now you can remove any additional permissions you granted, since any user can access their own information from the DMV.
Okay, with those out of the way, let’s move on to the notification system.
You may want to be notified about every single change, but most people would want to avoid the noise that would create and focus on objects that changed in any given time frame. For this purpose we’ll assume that you already have Database Mail up and running (review these tips if you need to set it up or it isn’t working correctly).
Notify on Every SQL Server DDL Change
If you want to be notified about every single change, the solution is simple: you can modify the DDL trigger to call msdb.dbo.sp_db_sendmail every time a DDL change takes place.
CREATE TRIGGER DDLTrigger_Sample ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, ALTER_SCHEMA, RENAME, CREATE_VIEW, ALTER_VIEW, CREATE_FUNCTION, ALTER_FUNCTION, ALTER_TABLE --, ... other events AS BEGIN SET NOCOUNT ON; DECLARE @EventData xml = EVENTDATA(), @ip varchar(48) = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')); DECLARE @subject nvarchar(max) = N'', @body nvarchar(max) = N'', @db sysname = DB_NAME(), @schema sysname = @EventData.value(N'(/EVENT_INSTANCE/SchemaName)', N'nvarchar(255)'), @object sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)', N'nvarchar(255)'), @event sysname = @EventData.value(N'(/EVENT_INSTANCE/EventType)', N'nvarchar(100)'); BEGIN TRY -- if e-mail errors, still want audit table updated SET @subject = @@SERVERNAME + N' : ' + @event + N' : ' + @object; SET @body = CONVERT(nvarchar(max), @EventData); -- you may want to add additional details to body, such as username, hostname, etc. EXEC msdb.dbo.sp_send_dbmail @profile_name = N'profile name', @recipients = N'DBA team alias', @subject = @subject, @body = @body; END TRY BEGIN CATCH PRINT 'error'; -- do real error handling here, like log exception somewhere END CATCH INSERT AuditDB.dbo.DDLEvents ( EventType, EventDDL, EventXML, DatabaseName, SchemaName, ObjectName, HostName, IPAddress, ProgramName, LoginName ) SELECT @event, @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand)', N'nvarchar(max)'), @EventData, DB_NAME(), @schema, @object, HOST_NAME(), @ip, APP_NAME(), SUSER_SNAME(); -- or ORIGINAL_LOGIN() or CURRENT_USER or ... END GO
Most people are not crazy about that idea, though, because then they’re getting e-mails at unpredictable times and they may get notified about a flurry of similar or even identical changes to the same object.
Notify on a Schedule for SQL Server DDL Changes
Setup here is a little more complex, but it is quite easy to configure things so that you are only notified once per time period about a change (or change type) for any given object.
First, remove any of those notify-on-every-change edits you made to your DDL trigger above.
Next, we’ll need to add two columns to our auditing table; one to track whether an event has already been included in a notification, and another to signify when the notification was sent out:
USE AuditDB; GO ALTER TABLE dbo.DDLEvents ADD NotifyStatus tinyint NOT NULL DEFAULT(0); GO UPDATE dbo.DDLEvents SET NotifyStatus = 2; -- already notified GO ALTER TABLE dbo.DDLEvents ADD NotifyDateTime datetime2(0); GO UPDATE dbo.DDLEvents SET NotifyDateTime = SYSUTCDATETIME(); GO
This means that new events will be eligible for the next notification, but everything that is already in the table will be considered “already notified” – this ensures that when you run this procedure the first time, you aren’t inundated with a big “all of time” e-mail.
Next, we’ll need a stored procedure that will mark all the new events since the last notification as “in process,” and then aggregate those and build a message with one line per object (and/or event type). The reason to mark them first is to carve out the set of events you’re going to notify on, and not have that set disrupted by any new events that come in while processing is happening.
CREATE PROCEDURE dbo.DDLEvents_Notify AS BEGIN SET NOCOUNT ON; -- probably want transaction handling here, though once -- an e-mail is queued, ROLLBACK can’t exactly undo it! UPDATE dbo.DDLEvents SET NotifyStatus = 1 -- in process WHERE NotifyStatus = 0; -- new IF @@ROWCOUNT > 0 BEGIN DECLARE @body nvarchar(max) = N'', @subject nvarchar(max) = @@SERVERNAME + N' : $x$ total DDL changes since $d$'; -- if you want a row per object + event type combination, uncomment EventType refs here: ;WITH agg(o,d,c) AS ( SELECT o = DatabaseName + N'.' + SchemaName + N'.' + ObjectName /* + N'(' + EventType + N')' */, d = MIN(EventDate), c = COUNT(*) FROM dbo.DDLEvents WHERE NotifyStatus = 1 GROUP BY DatabaseName, SchemaName, ObjectName --, EventType ), final(o,c,d,sumc) AS ( SELECT o,c,d = MIN(d) OVER(), sumc = SUM(c) OVER() FROM agg ) SELECT @body += CHAR(13) + CHAR(10) + o + N' (' + CONVERT(varchar(11),c) + ' change(s))', @subject = REPLACE(REPLACE(@subject,N'$x$',CONVERT(varchar(11),sumc)), N'$d$',CONVERT(char(10),d,120)+' '+CONVERT(char(8),d,108)) FROM final; EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA Team', @recipients = N'[email protected]', @body = @body, @subject = @subject; UPDATE dbo.DDLEvents SET NotifyStatus = 2, -- already notified NotifyDateTime = SYSUTCDATETIME() WHERE NotifyStatus = 1; END END GO
I created the DDL trigger in a database called TsD, then made some changes to two stored procedures, and then ran the procedure above. To avoid the complexities of sending mail outside of my VMs, I use a free desktop client called Papercut to simulate e-mail, and here is what I received:
Finally, you can just create a simple SQL Server Agent job that calls this procedure. How often you run this, of course, all depends on the trade-off between your need for up-to-the-minute information and your sensitivity to alert fatigue. At my last production shop, once a day was more than sufficient, but you may want notifications within a few minutes (and maybe only during business hours), so set up your schedule appropriately.
It is fairly straightforward to build a flexible notification system, so you can stay on top of DDL changes in your databases -- even when they are made outside of source control systems or build and deployment processes. If the alerts are coming too fast or not fast enough, it is very easy to adjust the schedule so that you’re made aware of changes at the exact pace that makes sense for you.
Read on for related tips and other resources:
- SQL Server DDL Triggers to Track All Database Changes
- Extending SQL Server DDL Triggers for more functionality: Part 1
- Extend SQL Server DDL Triggers for more functionality: Part 2
- Using a DDL Trigger to Block Schema Changes in SQL Server
- Using the EventData() Function with DDL triggers in SQL Server 2005
- Email alerts when new databases are created in SQL Server
- All Database Mail tips
Last Updated: 2018-08-27
About the author
View all my tips