Given that you are a DBA and 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 in enterprise level, you may need simpler one. Here it is.
I summarized three approaches using EVENT NOTIFICATION, TRACE and TRIGGER in the sample. I think EVENT NOTIFICATION preferable in that event notification is not in transaction scope and processed asynchronously. But the restriction is that the DB should MS-SQL 2005 and aboves.
(Sample: just open and execute extracted .sql files. )
It would be better if you know SQL-TRACE, TRIGGER, DDL and EVENT NOTIFICATION
The purpose is simple : whenever I CREATE/ALTER/DROP table or stored procedure, the DB records all of the queries.
Suppose that I send a query below:
CREATE TABLE TestTable (a int)
go
I expect to see the CREATE TABLE log like in the following image.
Set Up
create database hagendaaz GO use hagendaaz GO --//0) Enable Service Broker if it's disabled. ALTER DATABASE hagendaaz SET EnABLE_BROKER GO --//1) Create a queue to receive messages. CREATE QUEUE NotifyQueue with STATUS=ON, RETENTION = OFF; GO --//2) Create a service on the queue that references the event notifications contract. CREATE SERVICE NotifyService ON QUEUE NotifyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO --//3) Create a route on the service to define the address to which Service Broker sends messages for the service. CREATE ROUTE NotifyRoute WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL'; GO --//4) Create the event notification CREATE EVENT NOTIFICATION Notify_Table_Proc_Modifications ON DATABASE WITH FAN_IN --// send message only once FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE --// DDL event of your interest TO SERVICE 'NotifyService', --// broker service name 'current database'; --// 'broker_instance_specifier' GUID GO
--// Test CREATE TABLE TestTable (a int) go --// The Result select convert(xml,message_body)as XMLlog, * from dbo.NotifyQueue goThe data from 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 WMI service that WMI consumer software can see the log remotely.
Execute the '~_more_practical.sql' file to test a little more pratical version.
Event notification, trigger and trace, all respond to DDL events so it is possible to 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 trace creates trace file(.trc) that needs to be processed to show the log properly. So I prefered event notification in my situation.
But event notification also needs other consideration. There could be performance overhead associated with creating the XML-formatted event data and sending the event notification and event notification cannot be rolled back.
(For more information : Comparison between Event Notification vs Trigger vs Trace )
1. Created Article
| You must Sign In to use this message board. | |||||||||||
|
|||||||||||
|
|||||||||||
|
|||||||||||
