Auditing Your Database Changes

SQL Server can capture Database level Trigger information using XML.  I personally use this on my home PC’s because

  1. I’m poor and can’t afford one of those amazing DB Source control products that are out there on the market.
  2. I’m forgetful and always forget to check in  Source Control data.
  3. I don’t trust anyone else to remember either 🙂

So in an attempt to track what i do on my databases I have implemented the following.

I create a Database called Logs (imaginative) that  I place on all my instances of SQL Server.  Within this database I create all those fun DBA type tables that mean during meetings you can casually say, all our indexes are optimized, or we have never changed that.  This Audit Table is one of them.
CREATE TABLE [dbo].[SchemaEventLog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](120) NOT NULL,
[EventType] [varchar](120) NOT NULL,
[SchemaName] [varchar](120) NOT NULL,
[ObjectName] [varchar](120) NOT NULL,
[ChangeDate] [datetime] NOT NULL,
[ChangeUser] [varchar](120) NOT NULL,
[CommandText] [varchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I then run in the model database several scripts all based on the following:

CREATE trigger [change_Index] on database
for create_Index, alter_Index, drop_index
AS
BEGIN
set nocount on
declare @data xml
set @data = EVENTDATA()

insert into Logs.dbo.SchemaEventLog(DatabaseName,eventType,schemaName, ObjectName, changeUser, CommandText)

values(@data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(120)’)
, @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(120)’)
, @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(120)’)
, @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(120)’)
, @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(120)’)
, @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’) )
END
GO

These capture and record in the Logs db all changes to indexes, tables, stored procedures and functions.  When I create a new database as the triggers are placed in the new database due to them being in the model database.

The Trigger makes a call to the EVENTDATA() for the action this returns an XML Blob like

<EVENT_INSTANCE>
<EventType>CREATE_INDEX</EventType>
<PostTime>2013-09-11T17:36:27.290</PostTime>
<SPID>53</SPID>
<ServerName>PHIL-PCLAPTOP\SQL2008</ServerName>
<LoginName>Phil-PClaptop\Phil</LoginName>
<UserName>dbo</UserName>
<DatabaseName>MicroLoad</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>PK_WORLD</ObjectName>
<ObjectType>INDEX</ObjectType>
<TargetObjectName>Hello</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON” ANSI_PADDING=”ON” QUOTED_IDENTIFIER=”ON” ENCRYPTED=”FALSE” />
<CommandText>CREATE CLUSTERED INDEX [PK_WORLD] ON [dbo].[Hello]
(
[IDCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

The information that I’m interested in is taken via the .value xml command and placed in the audit table using a normal insert.  you could take all the information from the xml but I Like to limit it to a generic set.

It is possibly to extend the functionality of the triggers to include other object modifications but i normally find the key ones to use are tables, indexes, stored procedures and functions but this can be extended beyond these to almost any object.

There is a clean up script that I created that removes any entries that are older than 1 month (3 months/a year etc. depending on the DB use)  but this is a simple delete mechanism, run off a daily SQL Agent maintenance Job using the date predicate in the table.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s