By: Greg Robidoux | Updated: 2006-06-22 | Comments (1) | Related: > Triggers
Problem
With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system. There are several requirements these days such as Sarbanes Oxley, SAS70, GLBA, etc... that require you to keep a close eye on your database activity. With SQL Server 2000 you were limited to auditing login information and capturing data changes using DML (Data Manipulation Language) triggers, but there was no easy way to track DDL (Data Definition Language) changes in your database. As always there are third party tools that simplify the process, but most of the time the initial look is at what can be done natively with SQL Server.
Solution
With SQL Server 2005 DDL (Data Definition Language) triggers have been introduced. This type of trigger is different then INSERT, UPDATE and DELETE triggers, this trigger is fired when a change is made using such commands as ALTER, CREATE or DROP. The trigger is fired after the event occurs, so there is not an INSTEAD of trigger option like you have with DML triggers.
Creating a DDL trigger is just as simple as creating a DML trigger. Here is an example of a trigger that would fire whenever there is a DROP_TABLE or ALTER_TABLE event.
| CREATE TRIGGER tr_tableChange ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN do some activity based on event END |
Below is a sample listing of some of the events that can be captured. The list is much more extensive then this and can be found here or in SQL Server 2005 Books Online.
| Transact-SQL statement | Can be server scope (ON SERVER) | Can be database scope (ON DATABASE) |
|---|---|---|
| ADD_ROLE_MEMBER | X | X |
| ADD_SERVER_ROLE_MEMBER | X | |
| CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.) | X | X |
| ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.) | X | X |
| DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.) | X | X |
| CREATE_ASSEMBLY | X | X |
| ALTER_ASSEMBLY | X | X |
| DROP_ASSEMBLY | X | X |
| ALTER_AUTHORIZATION_SERVER | X | |
| ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.) | X | X |
| CREATE_CERTIFICATE | X | X |
| ALTER_CERTIFICATE | X | X |
| DROP_CERTIFICATE | X | X |
| CREATE_CONTRACT | X | X |
| DROP_CONTRACT | X | X |
| CREATE DATABASE | X | |
| ALTER DATABASE | X | X |
| DROP DATABASE | X |
source: SQL Server 2005 Books Online
In addition to individual events, DDL events can be collected by using event groups. This example here is triggered whenever there is DDL_LOGIN_EVENTS action. This includes DROP_LOGIN, CREATE_LOGIN and ALTER_LOGIN.
| CREATE TRIGGER tr_LoginEvents ON ALL SERVER FOR DDL_LOGIN_EVENTS AS BEGIN do some activity based on event END |
Here is a listing of the event groups. This list can be found here or look in SQL Server 2005 Books Online. The way this works is that each group consists of individual events. Also, some event groups contain other event groups. So for example the DDL_SERVER_LEVEL_EVENTS would capture all events that occur on the server.
source: SQL Server 2005 Books Online
Next Steps
- Take a look at the new DDL triggers in SQL Server 2005
- Plan your approach to auditing whether it be using DML, DDL and login auditing
- Determine if you need to collect server level or database level events
- Now that you have collected the data write queries and reports
- Lock down the data that you have collected, so the data can not be manipulated
- Look at third party tools to see if they offer a better or simpler solution
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2006-06-22
