SQL Server 2005 DDL Trigger Workbench

Robyn and Phil's latest workbench shows you how to track and log all database changes, including changes to tables, logins, users and queues, using SQL 2005 DDL triggers.

How about automatically tracking and logging all database changes, including changes to tables, views, routines, queues and so on? With SQL Server 2005 it isn’t that hard, and we’ll show how it is done. If you haven’t got SQL Server 2005, then get SQL Server Express for free. It works on that! While we’re about it, we’ll show you how to track all additions, changes and deletions of Logins and Database Users, using a similar technique.

Contents

Logging all changes to the database with source code.

To start off, we’ll write a simple trigger that tracks all database events. This will include creating, altering or dropping an APPLICATION_ROLE, ASSEMBLY, AUTHORIZATION_DATABASE, CERTIFICATE, CONTRACT, FUNCTION, INDEX, MESSAGE_TYPE, PARTITION_FUNCTION, PARTITION_SCHEME, PROCEDURE, QUEUE, REMOTE_SERVICE_BINDING, ROLE, ROUTE, SCHEMA, SERVICE, STATISTICS, TABLE, TRIGGER, USER, VIEW, or XML_SCHEMA_COLLECTION. It will also record the creation, or dropping of an EVENT_NOTIFICATION, SYNONYM, or TYPE and track all GRANT_DATABASE, DENY_DATABASE, and REVOKE_DATABASE DDL.

The new DDL triggers work very like the DML triggers you know and love. The most radical change is that the details of the event that fired the trigger are available only in XML format. You have to get serious with XPath queries to extract the XML which is in the format…

Before you do anything else, create a database called TestLogging

Now we will create a table that will be a change log. We will put in it the detail of each DDL SQL Statement and the user that did it. We’ll trap the login and the original login just to check for context switching. We’ll record the type of object, the type of event and the object name, and, of course the SQL that did it! Who needs source control?

Now we’ll create the trigger that fires whenever any database level DDL events occur. We won’t bother to record CREATE STATISTIC events.

Let’s create a table, view, and procedure, and then drop them, and after that, see what was recorded in the log.

Now, having done all that we can then see what happened. As you know, this is the only way you’ll ever see the current build statements for your tables! Now try changing the database objects via SSMS and have a look at the SQL DDL that gets executed!

To help, here is a better rendering of the log. We create an HTML table and format it up prettily.

Which gives this…

25 May 2007 10:47: dbo SIMPLETALK\RobynPage CREATE_TABLE PublicHouses (TABLE)
25 May 2007 10:47: dbo SIMPLETALK\RobynPage CREATE_VIEW vCambridgePubs (VIEW)
25 May 2007 10:47: dbo SIMPLETALK\RobynPage CREATE_PROCEDURE spInsertPub (PROCEDURE)
25 May 2007 10:47: dbo SIMPLETALK\RobynPage DROP_VIEW vCambridgePubs (VIEW)
25 May 2007 10:47: dbo SIMPLETALK\RobynPage DROP_PROCEDURE spInsertPub (PROCEDURE)
25 May 2007 10:47: dbo SIMPLETALK\RobynPage DROP_TABLE PublicHouses (TABLE)

Once we finish logging we can:

Of course, this can be very valuable for Database Development work.

Preventing changes to database objects

BOL seem to be very proud of their example code that prevents a table being altered, though, if you have security nailed down properly, this shouldn’t happen anyway.

Logging all changes to the Logins and database users

Another good use for triggers is to provide information about security events

Now we will write a trigger that inserts into our security log all server security events. There is a bug which prevents you just specifying all the security events, you have to list ’em.

Now we have to create another trigger in each database which recors all the database security changes.

Now everything is in place let’s test it. We will simulate an intruder’s cunning attempt to create himself as a login with sysAdmin rights and his gaining access as a database user. We could always prevent the transaction but that would just draw his attention to the trigger being there!

Now we can see that the whole activity has been logged. Because the initial CREATE LOGIN contained a password, it has not been recorded.

For further reading

Code formatted by the Simple-Talk Prettifier