Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: SQL server - Monitoring the users

    Hi,
    I'm new to sql server and I'm trying to use an Access Project.
    I need to know "Which user did What and When". I know the SQL Server creates transaction logs but as far as I know it doesn't record the user in the log record (please correct me if I'm wrong).
    There for although trans logs would be a great tool to know what happened it won't be usfull for me since it won't tell me who performed which action.
    I was wondering if its possible to configure the log files to also insert the user. If not is my only solution to creat a stored procedure that trigger after a table is changed that will creat somthing like the log file including the user name (it would be a waste of processor time and storege space!)


    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    We post the user Id and date/time stamp the row in the table when added or changed.

    It uses some table space. But it provides an audit trail and supports user activity monitoring.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    That will only show when the record was last edited by who, However it doesn't show the history of that record.
    What we need is to know which record was changed from what value to what and by whom.
    All the data I need is saved in transaction logs the only thing needed is the user.

    Does any one know if its possible to reconfigure Logs and add a field for user?



    Originally posted by Bruce A. Baasch
    We post the user Id and date/time stamp the row in the table when added or changed.

    It uses some table space. But it provides an audit trail and supports user activity monitoring.

    Good Luck,

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    The log files are not generally readable unless you purchase some special software. Do a search on log reader.

    What I have done in the past for a table that need close monitoring was the following:

    1. Create a near-duplicate of the original table with a suffix of "_log".
    2. Add two new fields to the new table: Edit and EditBy
    3. Create INSERT, UPDATE and DELETE triggers on the main table that insert everything from the appropriate columns into the new "audit table". In the appropriate trigger, you can specify whether the record being added to the _log table is an INSERT, UPDATE or a DELETE. You should also be able to specify the username (though how easy this is will depend on how the security of your database is managed).

    Be forewarned: this may be VERY costly (especially if you are doing mass updates to this table). It's not realistic to do it for each and every table, but if you can carefully define selected tables where records are updated one (or a few) at a time, then it might be a doable approach.

    Remember that a transaction cannot complete until the trigger completes; this is another reason to use the approach with great caution.

    You have been warned (now go do it!)!

    Regards,

    Hugh Scott

    Originally posted by Sia
    That will only show when the record was last edited by who, However it doesn't show the history of that record.
    What we need is to know which record was changed from what value to what and by whom.
    All the data I need is saved in transaction logs the only thing needed is the user.

    Does any one know if its possible to reconfigure Logs and add a field for user?

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your advise.
    How expensive are the utilities that enable reading the transaction files?






    Originally posted by hmscott
    The log files are not generally readable unless you purchase some special software. Do a search on log reader.

    What I have done in the past for a table that need close monitoring was the following:

    1. Create a near-duplicate of the original table with a suffix of "_log".
    2. Add two new fields to the new table: Edit and EditBy
    3. Create INSERT, UPDATE and DELETE triggers on the main table that insert everything from the appropriate columns into the new "audit table". In the appropriate trigger, you can specify whether the record being added to the _log table is an INSERT, UPDATE or a DELETE. You should also be able to specify the username (though how easy this is will depend on how the security of your database is managed).

    Be forewarned: this may be VERY costly (especially if you are doing mass updates to this table). It's not realistic to do it for each and every table, but if you can carefully define selected tables where records are updated one (or a few) at a time, then it might be a doable approach.

    Remember that a transaction cannot complete until the trigger completes; this is another reason to use the approach with great caution.

    You have been warned (now go do it!)!

    Regards,

    Hugh Scott

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Look for Lumigent Log Explorer. I see prices quoted on their site from $999 to $3,999.

    I myself have never tried the product and I am not trying to promote the product here.

    Regards,

    Hugh Scott

    Originally posted by Sia
    Thanks for your advise.
    How expensive are the utilities that enable reading the transaction files?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •