Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005

By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 6/22/2006

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" SQL Monitor – For database professionals who need results on Day One. Try it online. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....