Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

It help me a lot.


Learn more about SQL Server tools