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


By:   |   Updated: 2006-06-22   |   Comments (1)   |   Related: More > 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


Last Updated: 2006-06-22


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Comments For This Article




Friday, May 31, 2013 - 3:54:52 AM - Poonam Back To Top (25210)

It help me a lot.



download





Recommended Reading

SQL Server Trigger After Update for a Specific Value

Create a Simple SQL Server Trigger to Build an Audit Trail

SQL Server Trigger Example

How do SQL Server Triggers Work for Insert, Update, Delete and Truncate

How to find Updated Column in SQL Server Trigger








get free sql tips
agree to terms


Learn more about SQL Server tools