Extending SQL Server DDL Triggers for more functionality : Part 1

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),

aName   NVARCHAR(255)
EventXML     XML,
    DatabaseName NVARCHAR(255),
    Sche
m,
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),

 LoginName    NVARCHAR(255)
)
IPAddress    VARCHAR(32),
    ProgramName  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,

        LoginName,

ObjectName,
        EventDDL,

  (
            PARTITION BY Da
rn = ROW_NUMBER() OVER 
     
 tabaseName, SchemaName, ObjectName

ESC
        )        
    FROM
        
ORDER BY     EventDate 
DAuditDB.dbo.DDLEvents
)
SELECT cur.DatabaseName, 
  cur.SchemaName, 

n] = prev.EventDD
cur.ObjectName,
  cur.EventDate, 
  cur.LoginName,
  [PreviousVersi
oL, 
  [CurrentVersion]  = cur.EventDDL
FROM e AS cur
INNER JOIN e AS prev

me
 AND cur.ObjectName = prev.ObjectNam
ON cur.DatabaseName = prev.DatabaseName
 AND cur.SchemaName = prev.SchemaN
ae
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,

        EventDate,

ObjectName,
        LoginName,

 1) OVER 
        (
          PARTITION BY Datab
PreviousVersion = LAG(EventDDL
,aseName, SchemaName, ObjectName 
          ORDER BY     EventDate
        ),

         PARTITION BY DatabaseName
CurrentVersion = EventDDL,
        rn = ROW_NUMBER() OVER 
        (

 , SchemaName, ObjectName
          ORDER BY     EventDate DESC
        )        
    FROM

ate,
  LoginName, 
  [Previou
AuditDB.dbo.DDLEvents
)
SELECT DatabaseName, 
  SchemaName, 
  ObjectName,
  Event
DsVersion], 
  [CurrentVersion]

Version IS NOT NULL;
FROM e WHERE rn = 1 AND Previou
s

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

ABLE
AS
BEGIN
    SE
FOR CREATE_
TT NOCOUNT ON;

entData XML = EVENTDATA();
 DECLARE
   @s
DECLARE @E

vql 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

ABLE
AS
BEGIN
    SE
FOR CREATE_
TT NOCOUNT ON;

entData XML = EVENTDATA();
 DECLARE @s SY
DECLARE @E

vSNAME = @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 '

 @t)
   + ' FROM ' + QUOTENAME(@s) + '.' + QUOTENAME
+ QUOTENAME(@s) + '.' + QUOTENAME(N'Prefix' 
+(@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.

Source Collected from MSSQLTIPS.COM