Post reply

How to audit who read what tables and rows and when

  • December 1, 2006 at 12:22 pm

    #175485

    I am new to this DBA world. Anyone can give me a hint as to how can I audit who read what tables and what rows and when in SQL 2000? We have repository that only read access given to users but I need to know who access what and when. Is this possible? or I need other 3rd party utility to do this?

    Thanks,

    Harry.

  • December 1, 2006 at 12:35 pm

    #675250

    The only way that I am aware of would be for you to run profiler and capture all incomming traffic.  How busy is your system?  Keep in mind that this will eat up alot of disk space.  Just curious, but if you have granted all users SELECT access to your tables, why do you want to what they are reading? 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 1, 2006 at 9:58 pm

    #675324

    Thank you for the reply John. The reason behind this is we are hospital institution which required to record who access/read what data for audit purpose. I've never use the profiler before, when you said to capture all incoming traffic, how to do that? Thanks.

  • December 2, 2006 at 12:41 pm

    #675346

    Profiler is a utility that comes with SQL Server. You should read about it in Books Online and experiment with it. If you configure it to capture minimal data, users, query data, and times, then you can save some space. If this is for auditing, then you can configure Profiler using stored procedures and schedule them to run when the server starts. Then be sure you are saving the data off to disk or another SQL Server.

    This is a tough thing to do if you need to capture everything. IF you can alter the application and you use stored procedures for data access, you can build auditing into your application by inserting audit data into a table whenever you run a query.

  • December 2, 2006 at 6:41 pm

    #675357

    For high OLTP server profiler may cause performance issues when you are auding all calls, So As Steve advised use SQL trace using the procedures... and save the trace data locally on the server.

    You don't need to be guru write this code..you can setup the profiler and script it for sql server 2000 which will give you the script to run the trace...

    I believe there are some third party tools also available in the Market..

    MohammedU
    Microsoft SQL Server MVP

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

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