Post reply

Access Logging

  • October 10, 2006 at 5:01 pm

    #174855

    Hi all,

    Is there a way to log who accessed data in the table? I'm talking about running SELECT queries.

     

    Thanks a lot.

    ID

  • October 11, 2006 at 9:37 pm

    #665352

    You have a few choices here depending on what tools and money you have and also whether people connect to SQL using a shared login or individual logins.

    If there is a custom-built application that is being used, it could be instrumented to log access. This would require time and effort to code and test but it will not log access from applications like query analyzer.

    SQL Profiler can do what you are after. It can log access to particular tables with the log being written to a table or file. You would then need to have a process in place to manage and report on the log.

    There are also a number of third party tools that provide auditing capabilities. These usually come with management and reporting capabilities as well. Whilst not recommending it over any other product, an example of this is "SQL Compliance Manager" from Idera (http://www.idera.com/Products/SQLcm/). I have trialled this product and it is very easy to install and use.

  • October 12, 2006 at 1:12 am

    #665387

    In SQL 2000 you have three options:

    Run Profiler (as suggested)

    Create a trigger on the table

    Run SQL in C2 audit mode

    As with all solutions, you'll get a more comprehensive answer, if you put more detail into your question

  • October 12, 2006 at 1:20 am

    #665390

    Triggers are not an option to log SELECT statements.

    Be careful with C2 auditing. Once turned on, it needs to write its audit records. If the partition that it is using (wherever \mssql\data directory lives) becomes full, your SQL instance will stop.

    And I seem to remember that once turned on, you need to re-install SQL to turn it off.

  • October 12, 2006 at 3:52 am

    #665418

    I stand corrected.

    Another option is in your design - use stored procedures to run the select, and add a step to log the access.

  • October 12, 2006 at 9:14 am

    #665517

    Thanks to all of you for the info.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply