how to aduit database access?

background: sql2k on win2k3 server
i have an application which no longer has further development on it (so
that means no changes will be done thru front end interface of the
application). but new business policies require the aduit for database
access, such as who accesses the database at when and from where and so
on...(even for just read a table). I had expereinces writing triggers
to track inserts and edits, but not read only access.
is there a way to do so? some examples would be great! thank you.Of course updates and inserts can be easily audited with triggers. However,
if you attempt to audit every occurrance that every user reads from the
database, the size of your audit history will very soon exceed the size of
your database. For example, if a user queries 1000 records from a table,
will you make note of each individual record accessed or just the fact that
they accessed the table? These type events are perhaps better audited at the
application level. For example, User A looked at Purchase Order 122 @.
date/time.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1117216416.350834.242620@.g47g2000cwa.googlegroups.com...
>
> background: sql2k on win2k3 server
> i have an application which no longer has further development on it (so
> that means no changes will be done thru front end interface of the
> application). but new business policies require the aduit for database
> access, such as who accesses the database at when and from where and so
> on...(even for just read a table). I had expereinces writing triggers
> to track inserts and edits, but not read only access.
> is there a way to do so? some examples would be great! thank you.
>|||"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1117216416.350834.242620@.g47g2000cwa.googlegroups.com...
>
> background: sql2k on win2k3 server
> i have an application which no longer has further development on it (so
> that means no changes will be done thru front end interface of the
> application). but new business policies require the aduit for database
> access, such as who accesses the database at when and from where and so
> on...(even for just read a table). I had expereinces writing triggers
> to track inserts and edits, but not read only access.
> is there a way to do so? some examples would be great! thank you.
>
Here's an article discussing auditing in sql server 2k:
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
In particular, the last section speaks to using server-side traces for
auditing. This is particularly attractive in situation where you need fine
grained control over the which activity should be captured.|||thanks for the reply.
i'm only interested in table access audit level, not down to record
level, that will be to much as you indicated. I also agreed that this
kind of audit can easily be done on the app level, but as i said, there
will be no more app development! that's why i'm looking into database
side solution.|||Using SQL Profiler, you can configure a trace events such as object
references or execution of a stored procedure, and the columns returned can
include the user id. The output of the trace can be sent to a database table
or file, and you will need to tweak it so that it returns only the
information you really need. Perhaps you can implement a scheduled job that
periodically purges the table to keep it from getting too large. Of course
SQL Profiler will need be always running for this to work.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1117220601.221951.53120@.g44g2000cwa.googlegroups.com...
> thanks for the reply.
> i'm only interested in table access audit level, not down to record
> level, that will be to much as you indicated. I also agreed that this
> kind of audit can easily be done on the app level, but as i said, there
> will be no more app development! that's why i'm looking into database
> side solution.
>|||Tracking database activity
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
--
Programmer
"=== Steve L ===" wrote:

>
> background: sql2k on win2k3 server
> i have an application which no longer has further development on it (so
> that means no changes will be done thru front end interface of the
> application). but new business policies require the aduit for database
> access, such as who accesses the database at when and from where and so
> on...(even for just read a table). I had expereinces writing triggers
> to track inserts and edits, but not read only access.
> is there a way to do so? some examples would be great! thank you.
>|||If you are willing to look at 3rd party tools and have the budget there are
several auditing solutions available. www.lumigent.com is one for example.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1117216416.350834.242620@.g47g2000cwa.googlegroups.com...
>
> background: sql2k on win2k3 server
> i have an application which no longer has further development on it (so
> that means no changes will be done thru front end interface of the
> application). but new business policies require the aduit for database
> access, such as who accesses the database at when and from where and so
> on...(even for just read a table). I had expereinces writing triggers
> to track inserts and edits, but not read only access.
> is there a way to do so? some examples would be great! thank you.
>sql