Geeks With Blogs
A Developer's Expedition www.kenl.net

You have to love the xml type in SQL Server 2005.  Here's a simple way I found to make use of it: You can audit all the object/schema changes to the database with a simple database-level trigger.

First, create a very simple table (inside a schema I name 'Audit'):

CREATE TABLE [Audit].[Objects](

[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,

PRIMARY KEY CLUSTERED
(
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Then, the trigger:

CREATE TRIGGER [Trig_AuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO Audit.Objects(EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [Trig_AuditObjects] ON DATABASE

That's it.. now get a nice neat little xml entry in my table every time a DDL database level event happens;

<EVENT_INSTANCE>
    <EventType>ALTER_TABLE</EventType>
    <PostTime>2008-05-01T18:06:01.722</PostTime>
    <SPID>55</SPID>
    <ServerName>TestServ</ServerName>
    <LoginName>domain\username</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>Test2</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>Table1</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.Table1
    DROP COLUMN testremove
       </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

The EVENTDATA() function is provided by SQL Server inside a DDL trigger and provides all the data you see above as an xml document.

Having this during development is like a poor man's source control for schema changes.  It could come in very handy for forensic purposes when diagnosing post-rollout issues or accidental schema changes. 

I wish I could take credit for developing this cool little find. I found it surfing some time ago. I copied and never got around to testing it. I was pretty happy when I did. Thanks to the unknown coder of this one!

 

Posted on Thursday, May 1, 2008 9:33 PM | Back to top


Comments on this post: SQL 2005 Schema change auditing

# re: SQL 2005 Schema change auditing
Requesting Gravatar...
Thank you so much, it is very useful for me... Thanks again
Left by Vishwanath on Jun 20, 2008 1:39 AM

# SQL 2005 change auditing
Requesting Gravatar...
Thanks for sharing this informative article.
When it comes to audit the changes into SQL server database, I give preference to LepideAuditor for SQL server(http://www.lepide.com/sql-server-audit/ ) that provides a very close report of all critical changes made into SQL database. It helps to get the changes information into real time even at granular level.
However, I am trying to walk through this article also if it can be helpful in future.
Left by Michael alfred on Nov 30, 2014 7:29 PM

Your comment:
 (will show your gravatar)


Copyright © Ken Lovely, MCSE, MCDBA, MCTS | Powered by: GeeksWithBlogs.net