Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Extending SQL Server DDL Triggers for more functionality: Part 1


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | More > Triggers

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 09, 2013 - 2:42:44 AM - Elmozamil Elamir Hamid Back To Top

Hi,

I beleive as you mentioned the problem will be the trigger itself if it has been disabled by another user that has the permission.

Another porblem is about the size of the database and the control on it is table if I used it as a general database audit to audit tables, views, stroed procedure, login and the DML statements.

 

Thanks for this it really helps me to get many ideas to extend it to  audit all of my server and database.


Learn more about SQL Server tools