Customize SQL Server Notifications for DDL Changes

By: Aaron Bertrand | Updated: 2018-08-27 | Comments | Related: More > Database Administration

Problem

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.

Solution

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_Sample
Snapshot isolation transaction failed accessing database 'AuditDB' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
To avoid this error, you’ll want to issue this statement against your auditing database:

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 code:

DECLARE @ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
To this:

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)[1]',  N'nvarchar(255)'),
        @object  sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)[1]',  N'nvarchar(255)'),
        @event   sysname = @EventData.value(N'(/EVENT_INSTANCE/EventType)[1]',   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)[1]', 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:

Proof that my e-mail notification worked.

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.

Summary

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.

Next Steps

Read on for related tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-08-27