Extending SQL Server DDL Triggers for more functionality: Part 1

By: Aaron Bertrand | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Triggers

Problem

A previous tip, SQL Server DDL Triggers to Track All Database Changes, generated a lot of discussion involving ways to make the provided DDL trigger more useful. In this tip I address some of those questions with solutions to extend the DDL trigger functionality.

Solution

I wanted to post a follow-up to address a few of those questions and ideas from my earlier tip, as several of them would not be well represented in a comment.

Scott C pointed out that the DDL trigger may fail if the user does not have INSERT permissions on the audit table.

It's quite true; the DDL trigger will execute in the context of the caller. The caller must have adequate permissions to create/change the object in order to be successful (and also for the trigger to be fired in the first place), but may not have the permissions to insert into the audit table. Assuming you have the audit table set up this way:

CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);
GO

One way to ensure that the DDL trigger will never fail would be to grant read/write privilege to the public role (note that I do not grant update or delete, so that users can't - by default - cover up their tracks):

GRANT SELECT, INSERT ON OBJECT::dbo.DDLEvents TO [public];
GO

You can, of course, get more granular than that to avoid the public catch-all; how important that is will probably depend on how many individual logins/users you have (and whether you want to properly map all of those users in the audit database, assuming you are capturing DDL events centrally for more than one database).

**Johnny asked: how to get the latest version of a procedure and the one before it?**

In my original query, I simply returned the original and latest version of a procedure, ignoring all of the modifications in between. Sometimes it is more useful to compare the latest change with the version that existed immediately prior. Here is one way:

;WITH e AS
(
    SELECT
        EventDate,
        DatabaseName,
        SchemaName,
        ObjectName,
        LoginName,
        EventDDL,
        rn = ROW_NUMBER() OVER 
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate DESC
        )        
    FROM
        AuditDB.dbo.DDLEvents
)
SELECT cur.DatabaseName, 
  cur.SchemaName, 
  cur.ObjectName,
  cur.EventDate, 
  cur.LoginName,
  [PreviousVersion] = prev.EventDDL, 
  [CurrentVersion]  = cur.EventDDL
FROM e AS cur
INNER JOIN e AS prev
ON cur.DatabaseName = prev.DatabaseName
 AND cur.SchemaName = prev.SchemaName
 AND cur.ObjectName = prev.ObjectName
WHERE cur.rn = 1
AND prev.rn = 2;

And if you're running SQL Server 2012, you can do this slightly easier using the new LAG() function:

;WITH e AS
(
    SELECT
        DatabaseName,
        SchemaName,
        ObjectName,
        EventDate,
        LoginName,
        PreviousVersion = LAG(EventDDL, 1) OVER 
        (
          PARTITION BY DatabaseName, SchemaName, ObjectName 
          ORDER BY     EventDate
        ),
        CurrentVersion = EventDDL,
        rn = ROW_NUMBER() OVER 
        (
          PARTITION BY DatabaseName, SchemaName, ObjectName
          ORDER BY     EventDate DESC
        )        
    FROM
        AuditDB.dbo.DDLEvents
)
SELECT DatabaseName, 
  SchemaName, 
  ObjectName,
  EventDate,
  LoginName, 
  [PreviousVersion], 
  [CurrentVersion]
FROM e WHERE rn = 1 AND PreviousVersion IS NOT NULL;

Jaya asked how to create a mirror table with the same structure but a slightly different name.

This would presumably be used to serve as an archive table or perhaps an empty table used for partition switching. One way would be to reissue the same T-SQL command with the table name replaced. I'll use the following example as a DDL trigger *separate* from the one you use to audit all DDL commands:

CREATE TRIGGER DDL_CopyNewTable
    ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
 DECLARE
   @sql NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
   @t   SYSNAME       = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
 SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t);
 EXEC sp_executesql @sql;
END
GO

Some caveats:

This assumes a very simplistic table structure, with no foreign key constraints or other external dependencies.
The use of REPLACE() also makes some simplistic assumptions about the table name in use - e.g. it will not exist as part of another name in the code and, if it exists in a constraint name, that it is also ok to alter the name of the constraint.
If you encounter an error - e.g you've explicitly named a constraint without the name of the table, or the table with the prefix appended already exists, or the user doesn't have permission to create a table, the trigger will roll back the CREATE TABLE as well. Wrapping this in TRY/CATCH does not help.
The code will not capture indexes or constraints added after the fact - it only executes the source CREATE TABLE statement.
As a separate trigger, you cannot control what order the DDL triggers fire, so this second CREATE TABLE may or may not be audited. You may want to combine the logic if you want to do both, and as one trigger you can decide if you want to log the second CREATE TABLE. In my tests, both triggers fired correctly (the trigger I added second, that creates the copy of the table, fired first, and it was captured in the audit table).

A simpler approach might be to just issue a SELECT INTO, which will avoid some of the above issues (but not all):

ALTER TRIGGER DDL_CopyNewTable
    ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
 DECLARE @s SYSNAME = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
         @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
 DECLARE @sql NVARCHAR(MAX) = N'SELECT * INTO '
   + QUOTENAME(@s) + '.' + QUOTENAME(N'Prefix' + @t)
   + ' FROM ' + QUOTENAME(@s) + '.' + QUOTENAME(@t);
    PRINT @sql;
 EXEC sp_executesql @sql;
END
GO

Jaya also asked about maintaining this copy when the source table is modified in the future; I'll deal with that one next time. :-)

Conclusion

The previous tip brought about a very healthy conversation, with lots of follow-up questions. I've addressed a few of them here, and plan to address a few more in a future tip.

Next Steps

Review the following 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