|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Announcements
Want a new Job?
Chapters
Services
Feature Zones
|
Note: This is an unedited contribution. If this article is inappropriate,
needs attention or copies someone else's work without reference then please
Report This Article
Introduction
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.
Background
It would be better if you know SQL-TRACE, TRIGGER, DDL and EVENT NOTIFICATION
Purpose
The purpose is simple : whenever I CREATE/ALTER/DROP table or stored procedure, the DB records all of the queries.
CREATE TABLE TestTable (a int)
go
I expect to see the CREATE TABLE log like in the following image.
Using the codeSet 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 --// 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.
Comparison
Event notification, trigger and trace, all respond to DDL events so it is possible to record DB system modifications. History1. Created Article
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||