Archive

Posts Tagged ‘schema changes’

SQL Server – Auditing Schema Changes using DDL Triggers

15.07.2011 8 comments

Yesterday, I posted about SQL Server – Management Studio – Schema Changes History Report and Default Trace and their limitations. It does not capture the details of modification. i.e. When a new column is added to a table, you can find out when the changes to the Table were made using Default Trace, but it does not tell which column was added, or what command was used to add.

This information can be captured using DDL Triggers, it is a special kind of trigger which fires in response to DDL statements. A list of events that can be used to fire a DDL Trigger is available on BOL @ DDL Events for Use with DDL Triggers.

DDL Triggers can be of two scopes.

1. Server Level Trigger – By creating Trigger on Server, you can capture events for all Databases on the Server,

2. Database Level Trigger – This scope is limited to a single Database on which the Trigger is created.

 

DDL Trigger can capture the information about the EVENT that fired it by using EVENTDATA() function. EVENTDATA() returns an XML. For example below XML is returned when a DDL statement for CREATE TABLE is executed:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TABLE dbo.SalesYTD

(

CalendarYear  VARCHAR(7),

SalesYTD      DECIMAL(10,2)

)

EVENTDATA() XML:

<EVENT_INSTANCE>

       <EventType>CREATE_TABLE</EventType>

       <PostTime>2011-07-15T20:10:10.733</PostTime>

       <SPID>51</SPID>

       <ServerName>VGAJJAR</ServerName>

       <LoginName>sa</LoginName>

       <UserName>dbo</UserName>

       <DatabaseName>SqlAndMe</DatabaseName>

       <SchemaName>dbo</SchemaName>

       <ObjectName>SalesYTD</ObjectName>

       <ObjectType>TABLE</ObjectType>

       <TSQLCommand>

              <SetOptions   ANSI_NULLS=ONANSI_NULL_DEFAULT=ONANSI_PADDING=ON

                            QUOTED_IDENTIFIER=ONENCRYPTED=FALSE />

              <CommandText>CREATE TABLE dbo.SalesYTD

              (

CalendarYear  VARCHAR(7),

                     SalesYTD      DECIMAL(10,2)

              )</CommandText>

       </TSQLCommand>

</EVENT_INSTANCE>

We can capture this information to a table for Auditing using a DDL Trigger.

First of all, we need to create a table to store the information that will be extracted from EVENTDATA XML. It can be created as below:

— Audit_Log will be used to capture DDL Activities

CREATE TABLE Audit_Log

(

       EventTime            DATETIME,

       LoginName            SYSNAME,

       UserName             SYSNAME,

       DatabaseName         SYSNAME,

       SchemaName           SYSNAME,

       ObjectName           SYSNAME,

       ObjectType           VARCHAR(50),

       DDLCommand           VARCHAR(MAX)

)

Result:

Command(s) completed successfully.

Now, we can create the actual Trigger to capture DDL Events, I have captured only CREATE / ALTER and DROP events for DDL, for a complete list you can refer the link above:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE SqlAndMe

 

CREATE TRIGGER Log_Table_DDL

ON DATABASE

–ON ALL SERVER – by creating it on Server level,

–you can capture below events for all databases on server

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

–Fires only for CREATE / ALTER / DROP Table

AS

DECLARE       @eventInfo XML

SET           @eventInfo = EVENTDATA()

 

INSERT INTO Audit_Log VALUES

(

       REPLACE(CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),

       CONVERT(SYSNAME,

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),

       CONVERT(VARCHAR(50),

              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),

       CONVERT(VARCHAR(MAX),

              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

)

Result:

Command(s) completed successfully.

Once the Trigger is created, it will capture events in Audit_Log. Now let’s execute some DDL which can be captured:

USE SqlAndMe

 

CREATE TABLE MyTable

(

       ID_Number     INT,

)

 

ALTER TABLE MyTable

ADD Name VARCHAR(50)

 

DROP TABLE MyTable

 

CREATE VIEW v_Audit_Log AS

SELECT *

FROM   Audit_Log

 

Please note that the last statement creates a new VIEW, which will not be captured by Log_Table_DDL. Now let’s look at Audit_Log and see what has been captured:

SELECT *

FROM   Audit_Log

Result Set:

image

 

Hope This Helps!

Vishal

SQL Server – Management Studio – Schema Changes History Report

14.07.2011 No comments

Schema Changes History Report produces the list of changes done using DDL. It pulls this information from the Default Trace.

You can access the report from Management Studio:

1. Right Click on Database, for which you want to view the report,

2. Go to Reports > Standard Reports > Schema Changes History,

image

3. Schema Changes History report will open in a new window,

image

As you can see from the report, not all information is traced, for example, it traces ALTER, but it does not trace what was ALTERed about the object. i.e. ADD/DROP COLUMN.

You can also read the default trace using command:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT *

FROM   fn_trace_gettable

       ('C:Program FilesMicrosoft SQL Server' +

        'MSSQL10_50.MSSQLSERVERMSSQLLoglog.trc',

       default)

Default Trace does not track every single event on the Server, you can get a list of Events traced by Default Trace using below script:

SELECT CAT.Name AS Category, EVT.Name AS EventCaptured,

              COL.name AS ColumnCaptured

FROM   fn_trace_geteventinfo(1) AS TRA

INNER JOIN sys.trace_events AS EVT

       ON TRA.EventID = EVT.Trace_Event_ID

INNER JOIN sys.trace_columns AS COL

       ON TRA.ColumnID = COL.Trace_Column_ID

INNER JOIN sys.trace_categories AS CAT

       ON EVT.Category_ID = CAT.category_id

ORDER BY (1), (2), (3)

The main limitation of Default Trace is that it rolls over, so everything in trace won’t persist. It discards older data once it reaches the size limit.

Hope This Helps!

Vishal