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

How to add record thru command button in adp.

Hi,

In access database project where my tables are linked from MSSQL server, how can I add record from the FORM itself using command button. Bcz it is not like mdb files in access that you can just drag a command button onto the form and take an action like add, delete, print or find rec. I just came to know it from immediate window using following SQL line;

Docmd.RunSql "Insert......

This is ok but how it could be done thru a command button on FORM.

Thanks in advance.

With kind regards,
AshfaqueYou can use RunSQL with an ADP file. Or you can call an INSERT procedure on your SQL Server database.|||Hi blindman,

Bcz I am new to adp, can you please extend your help in more detail. I never used adp and its prodedure. How can I do it. Do I need to create a function / procedure? If so. How..Need to know badly. . Help in this regards will be higly appriciated.

Thanks a lot.
Ashfaque|||In an ADP application, no data is stored in Microsoft Access. It is all stored on the back-end server.

You can put code in the modules or forms in the ADP file, but it should be limited to affecting the behavior of the user interface. Your business logic should be implemented in stored procedures, views, and user-defined functions on your database server.

For an explanation of how to write TSQL code for SQL Server, look up VIEWS and STORED PROCEDURES in Books Online.|||Thanks again blindman,

I will check it.

With regards,
Ashfaque