Thomas Williams

Reporting Audit Changes to SQL Server 2005 objects

Following on from my last post on "Auditing Schema Changes to SQL Server 2005 objects", here's a simple bit of code that will take the EVENTDATA XML and transform it back into rows (note you need to have already run Richard's trigger and table creation script):

--EventData XML is element-centric. Below is an example, as elements will vary
--depending on what the "event" actually is:
/* 
<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2007-10-02T15:39:42.707</PostTime>
  <SPID>71</SPID>
  <ServerName>SERVER</ServerName>
  <LoginName>DOMAIN\USERNAME</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>TestDatabase</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>tblTest</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" 
     QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
      <CommandText>
        ALTER TABLE dbo.tblTest DROP CONSTRAINT DF_tblTest_Test
      </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>
*/

--just get back the necessary elements from the audit table - could also include
--SPID, server name, grantor, permissions, grantee, etc. depending on "event" 
SELECT  --most of the elements are cast as SYSNAME...difficult to find the *real* datatypes
        O.[EventData].value('(/EVENT_INSTANCE/EventType) [1]', 'SYSNAME') AS [EventType], 
        O.[EventData].value('(/EVENT_INSTANCE/PostTime) [1]', 'DATETIME') AS [PostTime], 
        O.[EventData].value('(/EVENT_INSTANCE/LoginName) [1]', 'SYSNAME') AS [LoginName], 
        O.[EventData].value('(/EVENT_INSTANCE/ObjectType) [1]', 'SYSNAME') AS [ObjectType], 
        --put together full object name from database, schema and object
        O.[EventData].value('(/EVENT_INSTANCE/DatabaseName) [1]', 'SYSNAME') + '.' + 
            O.[EventData].value('(/EVENT_INSTANCE/SchemaName) [1]', 'SYSNAME') + '.' + 
            O.[EventData].value('(/EVENT_INSTANCE/ObjectName) [1]', 'SYSNAME') AS [FullObjectName], 
        --actual command text (may be very long) 
        O.[EventData].value('(/EVENT_INSTANCE/TSQLCommand/CommandText) [1]', 'NVARCHAR(MAX)') AS [CommandText]
FROM    --using Richard's example, get data from the "Audit.Objects" table, with XML column 
        Audit.Objects O WITH (NOLOCK) 

My standard disclaimer is "it worked on my machine". Your mileage may vary.

The output from this query is a normal rowset and can be used in whatever reporting tool you favor (e.g. for me, Reporting Services).

p.s. Thanks to Whitney for the pointer on XML data types and queries!

Tags: sql server, audit, schema