Re: SQL 2005 - audit SELECT statements?
- From: "Uri Dimant" <urid@xxxxxxxxxxx>
- Date: Tue, 14 Feb 2006 08:44:09 +0200
Joe
Look at Dejan's example
For example, let?s say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Joe" <joe@xxxxxxxxxxx> wrote in message
news:ejSDFLNMGHA.2528@xxxxxxxxxxxxxxxxxxxxxxx
Hi!
Any ideas on how to audit *select* statements with SQL 2005?
This is a pretty common requirement with databases where one needs to keep
track of who-saw-what.
Oracle has had it for ages... wasn't able to find it anywhere for sql
2005... but need the functionallity.
Regards,
Joe
.
- References:
- SQL 2005 - audit SELECT statements?
- From: Joe
- SQL 2005 - audit SELECT statements?
- Prev by Date: Re: Managing SQL database rights using AD
- Next by Date: Re: Converting User SIDS from SQL 2000 to SQL 2005
- Previous by thread: Re: SQL 2005 - audit SELECT statements?
- Index(es):
Relevant Pages
|
