DDL Triggers

After a few public companies collapsed dramatically in 2002, the U.S. government enacted a new law called Sarbanes-Oxley, which demanded, among many other things, good IT practices. One of the big requirements of the law was to have a good change-management process and to know who installed a schema change into production and when it occurred. This required companies to spend hundreds of millions of dollars nationwide on new software to monitor changes and ensure good IT practices. Of course, they had the best motivation ever to be good citizens (if they didn't, they'd be in yellow jumpsuits and handcuffs).

Part of those millions of dollars spent was on monitoring software for SQL Server to watch for unauthorized changes. In this section, you'll learn how to use DDL triggers to alleviate the need for some of these auditing tools. We've had DML triggers since modern-day database systems were conceived, but what are DDL triggers? A DDL event includes any event at a database or server-level that alters or creates a database object or server object (like a login). For example, with a DDL trigger, you can configure the trigger to e-mail you or to log whenever a table is created.

DDL Trigger Syntax

The syntax for a DDL trigger is much like that of a DML trigger. With a DDL trigger, though, instead of monitoring an INSERT statement, you monitor for a CREATE event, for example. The generic syntax looks like this:

 CREATE TRIGGER <trigger_name> ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] } 

Most of this syntax you probably recognize from DML triggers, so we'll focus mostly on the DDL-specific syntax. You have two scopes you can specify in a DDL trigger: ALL_SERVER or DATABASE. As the names imply, the ALL_SERVER scope monitors all server-level events, and the DATABASE option monitors database-level events.

The other important configurable part of the syntax is after the FOR clause. After the FOR clause, you would specify what you'd like to monitor in the database or on the server with the DDL trigger option. Examples of this vary based on what level of DDL trigger you have. The upcoming sections will break down these examples.

Database Triggers

Database DDL triggers are executed when you create, drop, or alter an object at a database-level, like a user, table, stored procedure, Service Broker queue, or view, to name a few. If you want to trap all database DDL events, you use the trigger option in the syntax mentioned earlier for FOR DDL_DATABASE_LEVEL_EVENTS. The events are hierarchical, and the top-level database trigger types are shown in the following list. Under the trigger types mentioned in list, you can get much more granular. For example, rather than trapping all events when any type of table event occurs, you can narrow it down to only raising the DDL event when a table is dropped by using the DROP_TABLE trigger option.

• DDL_TRIGGER_EVENTS

• DDL_FUNCTION_EVENTS

• DDL_SYNONYM_EVENTS

• DDL_SSB_EVENTS

• DDL_DATABASE_SECURITY_EVENTS

• DDL_EVENT_NOTIFICATION_EVENTS

• DDL_PROCEDURE_EVENTS

• DDL_TABLE_VIEW_EVENTS

• DDL_TYPE_EVENTS

• DDL_XML_SCHEMA_COLLECTION_EVENTS

• DDL_PARTITION_EVENTS

• DDL_ASSEMBLY_EVENTS

To create a trigger that would audit for any stored procedure change, deletion, or creation, the CREATE TRIGGER statement would look like the following:

 CREATE TRIGGER ChangeWindow ON DATABASE FOR DDL_PROCEDURE_EVENTS AS -- Trigger statement here 

As mentioned earlier, you can create granular triggers on certain events by using the event type after the FOR keyword. For example, to monitor for anytime a DROP TABLE, CREATE TABLE, or ALTER TABLE statement is issued, you could use the following code:

 CREATE TRIGGER ChangeWindow ON DATABASE FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE AS -- Trigger statement here 

Finally, you can monitor all changes by using the DDL_DATABASE_LEVEL_EVENTS event type:

 CREATE TRIGGER ChangeWindow ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS -- Trigger statement here 

An important function in your DDL trigger toolbox is the EVENTDATA() system function. The EVENT-DATA() system function is raised anytime a DDL trigger is fired at any level and outputs the event type, the user who executed the query, and the exact syntax the user ran. The function outputs this data in XML format:

 <EVENT_INSTANCE>   <EventType>CREATE_USER</EventType>   <PostTime>2006-07-09T12:50:16.103</PostTime>   <SPID>60</SPID>   <ServerName>BKNIGHT</ServerName>   <LoginName>BKNIGHT\Owner</LoginName>   <UserName>dbo</UserName>   <DatabaseName>AdventureWorks</DatabaseName>   <ObjectName>brian</ObjectName>   <ObjectType>SQL USER</ObjectType>   <DefaultSchema>brian</DefaultSchema>   <SID>q7ZPUruGyU+nWuOrlc6Crg==</SID>   <TSQLCommand>     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED=" FALSE" />     <CommandText>CREATE USER [brian] FOR LOGIN [brian] WITH DEFAULT_SCHEMA = [dbo]</CommandText>   </TSQLCommand> </EVENT_INSTANCE> 

You can then either pull all the data from the EVENTDATA() function and log it into a table as an XML data type or pull selective data out using an XPATH query. To do an XML XPATH query in SQL Server 2005, you would specify the path to the XML node. In DDL triggers, the key elements from the EVENTDATA() function are the following:

• EventType - The type of event that caused the trigger

• PostTime - The time the event occurred

• SPID - The SPID of the user that caused the event

• ServerName - The instance name that the event occurred on

• LoginName - The login name that performed the action that triggered the event

• UserName - The username that performed the action that triggered the event

• DatabaseName - The database name the event occurred in

• ObjectType - The type of object that was modified, deleted, or created

• ObjectName - The name of the object that was modified, deleted, or created

• TSQLCommand - The T-SQL command that was executed to cause the trigger to be run

To pull out selective data, you could use code like the following to do an XPATH query. You would first pass in the fully qualified element name, like /EVENT_INSTANCE/TSQLCommand, and the [1] in the following code means to pull out the first record. Since there is only one record in the EVENTDATA() function, you will always pull the first record only. The EVENTDATA() function will only be available to you in the scope of the trigger. If you were to run this query outside the trigger, it would return NULL.

 CREATE TRIGGER RestrictDDL ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS EXECUTE AS USER = 'DBO' DECLARE @errordata XML SET @errordata = EVENTDATA() SELECT @errordata GO 

You can use the ROLLBACK command in a DDL trigger to cancel the command that the user ran. You can also wrap this in a conditional IF statement to conditionally roll the statement back. Typically, though, you'll see DDL triggers log that the event occurred and then potentially roll the command back if the user did not follow the correct change procedures.

We'll now walk through a complete example of how you could use DDL triggers to monitor for changes that occur in a database. The type of triggers that you're trying to monitor for are any database-level events, such as table or security changes. In the event of a change, you wish to log that event into a table. If the user is not logged in as the proper account, you wish to roll the change back. This way, you can prevent users in the sysadmin role from making changes. The table that you want to log the change events into is called DDLAudit. To create the table, use the following syntax or download the complete example on www.wrox.com:

 CREATE TABLE DDLAudit (   AuditID       int           NOT NULL identity                               CONSTRAINT DDLAuditPK                               PRIMARY KEY CLUSTERED,   LoginName     sysname       NOT NULL,   UserName      sysname       NOT NULL,   PostDateTime  datetime      NOT NULL,   EventType     varchar(100)  NOT NULL,   DDLOp         varchar(2500) NOT NULL ) 

You're now ready to create the trigger to log into the table. Most SQL Server environments only allow changes to the database between certain maintenance window hours. You can use the following DDL trigger to prevent changes outside the 8 p.m. to 7 a.m. maintenance window. Changes made at any other time will be rolled back. If you are inside the maintenance window, the change will be logged to a DDLAudit table.

 CREATE TRIGGER ChangeWindow ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @errordata XML SET @errordata = EVENTDATA() INSERT dbo.DDLAudit        (LoginName,         UserName,         PostDateTime,         EventType,         DDLOp) VALUES   (SYSTEM_USER, ORIGINAL_LOGIN(), GETDATE(),    @errordata.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),    @errordata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(2500)')) IF DATEPART(hh,GETDATE()) > 7 AND DATEPART(hh,GETDATE()) < 20 BEGIN   RAISERROR ('You can only perform this change between 8PM and 7AM. Please try this change again or contact Production support for an override.', 16, -1)   ROLLBACK END 

Note that in this code, you trap the login being used, as well as the original login to check for context switching (EXECUTE AS). With the trigger now created, test the trigger by running a simple DDL, command such as:

 CREATE table TriggerTest (Column1 int) 

If you executed this command after 7 a.m. and before 8 p.m., you would receive the following error, and the CREATE statement would roll back. You could look at the tables in Management Studio, and you should not see the TriggerTest table.

  (1 row(s) affected) Msg 50000, Level 16, State 1, Procedure ChangeWindow, Line 22 You can not perform this action on a production database. Please contact the production DBA department for change procedures. Msg 3609, Level 16, State 2, Line 2 The transaction ended in the trigger. The batch has been aborted. 

If you were to run the statement before 7 a.m. or after 8 p.m., you would only see (1 row(s) affected), meaning that the change was logged but you were able to successfully perform the action. You can test this by changing the server's time in Windows. After you try to create a few tables or make changes, select from the DDLAudit table to see the audited records.

There is little performance effect to this type of trigger, since generally DDL events rarely happen. The trigger can be found in Management Studio by selecting the individual database and then selecting ProgrammabilityDatabase Triggers. You can create a script of the trigger to modify an existing trigger or you can delete the trigger by using Management Studio.

Of course, you are still going to want to occasionally run a DDL statement in production by overriding the trigger. To allow access, you must only temporarily turn off the trigger using the following syntax for database or server-level triggers:

 DISABLE TRIGGER ALL ON DATABASE GO DISABLE TRIGGER ALL ON ALL SERVER 

After the override, you can enable the triggers again by running the following syntax. You could replace the keyword ALL with the specific trigger name to enable or disable the individual trigger.

 ENABLE TRIGGER ALL ON DATABASE GO ENABLE Trigger ALL ON ALL SERVER; 

Server Triggers

Server-level triggers operate the same way as database triggers but are monitored for server configuration, security, and other server-level changes. The following list shows you the top-level events, but these too are hierarchical. For example, you can monitor any login changes, as you'll see in an upcoming example.

• DDL_DATABASE_EVENTS

• DROP_DATABASE

• DDL_ENDPOINT_EVENTS

• CREATE_DATABASE

• DDL_SERVER_SECURITY_EVENTS

• ALTER_DATABASE

We'll walk through another quick example. As we mention in Chapter 9, if you are in the sysadmin role, you will be able to perform any function you wish on the server. With this DDL trigger, you can ensure that only a single login will be able to perform login-type events, like creating logins. If anyone else tries to create, modify, or delete a login, it will be rolled back. Of course, you'd have to implement additional security measures if this is your actual requirement, like protecting the DDL trigger from change, but we'll keep it simple for this example.

 CREATE TRIGGER PreventChangeTrigger ON ALL SERVER FOR DDL_LOGIN_EVENTS AS IF SUSER_NAME() != 'BKNIGHT\OWNER' BEGIN   RAISERROR ('This change can only be performed by the server owner, Brian Knight. Please contact him at extension x4444 to follow the procedure.', 16, -1) ROLLBACK END 

If someone other than the BKNIGHT\OWNER login were to attempt a login change, they would receive the following error. You can issue a permission context switch (EXECUTE AS LOGIN) to test out the trigger in your development environment.

 Msg 50000, Level 16, State 1, Procedure PreventChangeTrigger, Line 9 This change can only be performed by the server owner, Brian Knight. Please contact him at extension x4444 to follow the procedure. Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. 

DDL server triggers can be found in Management Studio under Server ObjectsTriggers. Like the database triggers, you can only script the trigger for modifications and delete the trigger from Management Studio.

Trigger Views

The Management Studio interface is still slightly lacking in what you can accomplish with DDL triggers, so a DBA must often go T-SQL as a management interface. One of the nice views available to show you all the database-level DDL triggers is sys.triggers; for server-level triggers, you can use sys.server_triggers. Between these two views, you can quickly see what views your server has installed on it with a query like this one:

 SELECT type, name, parent_class_desc FROM sys.triggers WHERE parent_class_desc = 'DATABASE' UNION SELECT type, name, parent_class_desc FROM sys.server_triggers WHERE parent_class_desc = 'SERVER'