Introduction
Given that you are a DBA, you would need to know how other programmers are modifying the DB tables and Stored Procedures for the purpose of audit or just curiosity. Though there could be a lot of solutions for IT audit on the enterprise level, you may need a simpler one. Here it is.
I summarized three approaches using Event notifications, Traces, and Triggers in the sample. I think Event notifications are preferable in that they are not in the transaction scope and are processed asynchronously. But the restriction is that the database should be MS SQL Server 2005 and above. (Sample file: just open and execute the extracted .sql files.)
Background
It would be better if you are familiar with SQL-Trace, Triggers, DDL, and Event notifications.
Purpose
The purpose is simple: whenever I CREATE
/ALTER
/DROP
a table or Stored Procedure, the DB records all of the queries.
Suppose that I send the query below:
CREATE TABLE TestTable (a int)
go
I expect to see the CREATE TABLE log like in the following image:
Using the code
Set up:
create database hagendaaz
GO
use hagendaaz
GO
ALTER DATABASE hagendaaz SET EnABLE_BROKER
GO
CREATE QUEUE NotifyQueue with STATUS=ON, RETENTION = OFF;
GO
CREATE SERVICE NotifyService ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE ROUTE NotifyRoute WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL';
GO
CREATE EVENT NOTIFICATION Notify_Table_Proc_Modifications
ON DATABASE
WITH FAN_IN
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_PROCEDURE,
ALTER_PROCEDURE, DROP_PROCEDURE
TO SERVICE 'NotifyService',
'current database';
GO
Test:
CREATE TABLE TestTable (a int)
go
select convert(xml,message_body)as XMLlog, * from dbo.NotifyQueue
go
The data from the XMLlog column looks like this:
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2008-03-25T15:22:43</PostTime>
<LoginName>dev</LoginName>
<UserName>dbo</UserName>
...
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE TestTable (a int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
This XML is a SOAP message broker service transfer. This message can be sent to a WMI service so that a WMI consumer software can see the log remotely. Execute the '~_more_practical.sql' file to test a little more practical version.
Comparison
Event notifications, triggers, and traces, all respond to DDL events, so it is possible to easily record DB system modifications. But triggers are processed synchronously, within the scope of the transactions that cause them to fire. Unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction. Additionally, a trace creates a trace file (.trc) that needs to be processed to show the log properly. So I preferred event notifications in my situation. But event notifications also need other consideration. There could be a performance overhead associated with creating the XML-formatted event data and sending the event notification, and event notifications cannot be rolled back. (For more information: Event Notification vs. Trigger vs. Trace)
History