auditting select statement

We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets 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).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:

> Tracey
> Take a 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 sa
me
> 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 l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>