Enabling SQL audit Logging for SELECT queries

Hi all,
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the divantages we see here are performance issues due to profiler
being run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the divantages we see here are performance issues due to profil
er
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>