Implementing logging of user actions which will effect DB - Best Practice

Hi
Looking for some very general advice. I have a classic asp content
management application which I am porting to ASP.NET. The database is
SQL server, and it is hoped that we can leverage this without
significant alterations. There is approx 30 users accessing and
managing information using the application. Currently users log on to
the system using group logins. A general connection string is used with
all communication with the database.

One of the new requirements is that all activities carried out by users
which have any effect the data stored in the database be logged and
traceable back to that particular user.

I am considering two possible approaches to the new design, with regard
to this logging requirement.

1) In some way use (leverage), the SQL logging functionality. This
would mean that the DBA would have to maintain user accounts, this
could be an issue.
2) Implement in code a standardized logging function, which could
simply be subclassed by buttons used in the interface for saving
records etc., which would record the user and his actions in a new
table.

Advice on the best way to tackle this issue would be much appreciated.

re: Implementing logging of user actions which will effect DB - Best Practice

Hello philip.mckee@gmail.com,

The second case is most common way for it. But I recomend not to subclass
the button, but the methods that is responsible for this.
Override the methods that is responsible for saving data with you method
with logging calls
[color=blue]
> Hi
> Looking for some very general advice. I have a classic asp content
> management application which I am porting to ASP.NET. The database is
> SQL server, and it is hoped that we can leverage this without
> significant alterations. There is approx 30 users accessing and
> managing information using the application. Currently users log on to
> the system using group logins. A general connection string is used
> with
> all communication with the database.
> One of the new requirements is that all activities carried out by
> users which have any effect the data stored in the database be logged
> and traceable back to that particular user.
>
> I am considering two possible approaches to the new design, with
> regard to this logging requirement.
>
> 1) In some way use (leverage), the SQL logging functionality. This
> would mean that the DBA would have to maintain user accounts, this
> could be an issue.
> 2) Implement in code a standardized logging function, which could
> simply be subclassed by buttons used in the interface for saving
> records etc., which would record the user and his actions in a new
> table.
> Advice on the best way to tackle this issue would be much appreciated.
>[/color]
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

re: Implementing logging of user actions which will effect DB - Best Practice

If you are on a domain, you can use trusted connections and then on SQL
Server suser_sname() will give you the domain login. Then you can use a
trigger to log information to a logging table about who did what and your
logging does not depend on the application implementing the logging calls.

<philip.mckee@gmail.com> wrote in message
news:1144061060.427498.36910@j33g2000cwa.googlegro ups.com...[color=blue]
> Hi
> Looking for some very general advice. I have a classic asp content
> management application which I am porting to ASP.NET. The database is
> SQL server, and it is hoped that we can leverage this without
> significant alterations. There is approx 30 users accessing and
> managing information using the application. Currently users log on to
> the system using group logins. A general connection string is used with
> all communication with the database.
>
> One of the new requirements is that all activities carried out by users
> which have any effect the data stored in the database be logged and
> traceable back to that particular user.
>
> I am considering two possible approaches to the new design, with regard
> to this logging requirement.
>
> 1) In some way use (leverage), the SQL logging functionality. This
> would mean that the DBA would have to maintain user accounts, this
> could be an issue.
> 2) Implement in code a standardized logging function, which could
> simply be subclassed by buttons used in the interface for saving
> records etc., which would record the user and his actions in a new
> table.
>
> Advice on the best way to tackle this issue would be much appreciated.
>[/color]