Extending SQL Server DDL Triggers for more functionality: Part 1
By: Aaron Bertrand | Updated: 2013-05-08 | Comments (1) | Related: 1 | 2 | More > Triggers
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.
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
;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)', 'NVARCHAR(MAX)'), @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(255)'); SELECT @sql = REPLACE(@sql, @t, N'Prefix' + @t); EXEC sp_executesql @sql; END GO
- 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 TABLEas well. Wrapping this in
TRY/CATCHdoes not help.
- The code will not capture indexes or constraints added after the fact - it only executes the source
- As a separate trigger, you cannot control what order the DDL triggers fire, so this second
CREATE TABLEmay 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)', 'NVARCHAR(255)'), @t SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)', '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. :-)
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.
- Review the following tips and other resources:
- SQL Server DDL Triggers to Track All Database Changes
- Using the EventData() Function with DDL triggers in SQL Server 2005
- Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers
- Designing DDL Triggers
- Implementing DDL Triggers
- Auditing in SQL Server 2008
Last Updated: 2013-05-08
About the author
View all my tips