Create DDL table in SQL Server 2005 to audit DDL trigger activity

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1312369,00.html?track=NL-417&ad=638703&asrc=EM_NLT_3593059&uid=6118215

Robert Sheldon
05.05.2008

Prior to SQL Server 2005, you could define only Data Manipulation Language (DML) triggers. Those triggers fired when you ran a DML statement such as UPDATE or DELETE. With the release of SQL Server 2005, you can now define Data Definition Language (DDL) triggers. These types of triggers fire when you run a DDL statement such as CREATE TABLE and ALTER VIEW, and that makes it easy to use the DDL triggers to audit DDL events in SQL Server.

One method you can use to audit DDL events is to first create a table to store the event-related data and then create a DDL trigger to log the events. In this article, I explain the steps and give examples that demonstrate each concept. For the examples, I will create an audit table and DDL trigger in the AdventureWorks sample database that ships with SQL Server 2005. Note that the article assumes you're already familiar with Transact-SQL and DDL concepts.

Creating the DDL audit table

The audit table stores the event-related information that is generated each time a specified type of DDL event fires. For example, if you delete a view from your database, a DROP_VIEW event is generated. You can use a DDL trigger to capture the event information and store it to your table.

The audit table should, at the very least, contain an XML column that stores the event-related data. You'll see later how SQL Server generates the data in an XML format. The table, of course, should also include a primary key column. The following statement creates a basic audit table in the AdventureWorks database:

 

CREATE TABLE dbo.EventLog
(EventID INT PRIMARY KEY IDENTITY,
EventInstance XML NOT NULL)
GO

 

Notice that I've included the EventID column as the primary key and how the EventInstance column will hold the XML data related to each event. Every time a DDL event is generated, a row will be added to the table. You can then retrieve the contents of the EventInstance column to view information about a specific event.

Creating the DDL trigger

After you create your audit table, you should define the DDL trigger. The following CREATE TRIGGER statement defines a trigger that will insert event-related data into the EventLog table each time a DDL event occurs within the AdventureWorks database:

CREATE TRIGGER LogEvents
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO dbo.EventLog (EventInstance)
VALUES (EVENTDATA())

 

Let's take a look at each line of the code to better understand what I've done. The CREATE TRIGGER clause simply identifies the name of the new trigger, which in this case is LogEvents. The second line -- ON DATABASE -- indicates that the trigger will be created at the database level. You can also create triggers that fire whenever a DDL event occurs on the server, in which case you would use the ON ALL SERVER option. However, for this example, we're concerned only with DDL events related to the AdventureWorks database.

The next line of code -- AFTER DDL_DATABASE_LEVEL_EVENTS -- is the AFTER clause. The first part of this clause is the AFTER keyword, which indicates that the trigger should fire only after the related operation (specified in the second part of the clause) has executed successfully. Instead of specifying AFTER, you can specify the FOR keyword, which means that the event fires as soon as the DDL event occurs. In this case, I prefer to log these operations only after they have run successfully.

The next part of the AFTER clause specifies the event type or group. This is where you stipulate which DDL events should cause the trigger to fire. I chose the DDL_DATABASE_LEVEL_EVENTS option (an event group) because I want to audit all DDL events at the database level. However, you can choose another group or individual event types. If you specify more than one event group or type, use commas to separate the options. For details about each event type and group, see the "CREATE TRIGGER (Transact-SQL)" topic in Microsoft SQL Server Books Online.

After I specify the event group, I add an AS keyword, followed by an INSERT statement, which inserts a row into the EventLog table each time the trigger fires. I get the value for the EventInstance column by calling the EVENTDATA() system function, which retrieves event-related data (in XML format). When the event fires, the function provides the necessary value.

That's all there is to setting up a basic auditing solution. Now let's test it to verify the results.

Testing the auditing solution

The best way to test the solution is to run a couple DDL statements against the AdventureWorks database. The following statements create the Person.Contact2 table and then drop the table:

 

SELECT FirstName, LastName, EmailAddress
INTO Person.Contact2
FROM Person.Contact
GO
DROP TABLE Person.Contact2

Both statements should generate DDL events on the AdventureWorks database and subsequently fire the LogEvents trigger. To verify whether the trigger correctly logged these two events, simply run the following SELECT statement:
SELECT * FROM dbo.EventLog

The statement should return two rows. For each row, the EventInstance column should include the XML related to the specific event. If you were to view the XML for the first row, you should see the following results:

Notice that the event data includes the event type, the object that was created, and the command used to create the table, along with other information. If you prefer to retrieve only specific information from the EventInstance column, you can use XQuery expressions to access individual element values. For example, the following SELECT statement uses the XML value() method to retrieve the event type, schema name and object name:

 

SELECT EventID,
EventInstance.value('(//EventType)[1]', 
'nvarchar(30)') AS EventType,
EventInstance.value('concat((//SchemaName)[1], 
".", (//ObjectName)[1])', 'nvarchar(60)') 
AS ObjectName
FROM dbo.EventLog

 

As you can see, I call the value() method by first specifying the EventInstance column, which is defined with the XML data type. I can call any XML method in this way. The value() method includes two arguments. The first identifies the XML element whose value I want to retrieve. The second identifies the data type. The first argument should be enclosed in parentheses and followed by [1] because the value() method returns only scalar values. Even if the specified element is unique within the XML (as in this case), you must still specify the [1]. When you run this SELECT statement, you should receive the following results:

EventID EventType ObjectName 1 CREATE_TABLE Person.Contact2 2 DROP_TABLE Person.Contact2

(2 row(s) affected)

The results show the CREATE_TABLE and DROP_TABLE event types, exactly what you would expect based on the DDL statements I ran. Because SQL Server now supports Data Definition Language (DDL) triggers, it's quite easy to audit the events generated by these types of statements.

As I've shown you here, a simple approach to auditing events is to create an audit table in SQL Server and define a DDL trigger. However, you can use DDL triggers to take actions other than inserting a row in an audit table. For example, you can send an email message to a specified recipient when a particular DDL event occurs. When you create a DDL trigger, you can define one or more Transact-SQL statements that do whatever you want them to do, which lets you create triggers that can perform a wide variety of tasks.