Post reply

Auditing Select on a table

  • May 9, 2003 at 8:56 am

    #82176

    Hi all,

    Is there any way wherein I can see who has run a select ... on a table?

    The trigger solution is useful for inserts/updates/deletes, but what is the method to audit a select.

    Thanks.

    Diwakar Cirium

    --

    Diwakar

    --

  • May 9, 2003 at 9:14 am

    #456676

    How you tried Profiler?

  • May 9, 2003 at 9:22 am

    #456679

    Basically, what I want to do is this :

    whenever a user/operator tries running a select on a table either from a front-end (web/other) or query analyser or similar tool) , I want to store that information in an Audit table.

    For inserts/deletes/updates, I can use a trigger to store information in the Audit table, and I want to vet a select as well.

    If I have to use a profiler, I have to keep it running in the background, store the results in a trace table, write triggers on this trace table to look for a select on the target table... too much work.

    So, I don't want to use the Profiler.

    --

    Diwakar Cirium

    --

  • May 9, 2003 at 9:35 am

    #456682

    As you know, trigger doesn't work with select statement.

    Maybe using third party tools to examine the transaction log files is another option.

    Edited by - Allen_Cui on 05/09/2003 09:35:59 AM

  • May 9, 2003 at 9:39 am

    #456683

    Yeah, I was just hoping that someone would have found a different way of doing this.

    Thanks for your response Allen_Cui, much appreciated.

    --

    Diwakar Cirium

    --

  • May 9, 2003 at 10:45 am

    #456694

    You can also use the various trace stored procedures that are provided... same functionality as Profiler but without the app running interactively. If you tailor your traces, you can minimize the amount of information collected, but you're right in that there will be some extraneous information that you'll have to filter through.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • May 12, 2003 at 6:44 am

    #456817

    Place your query in a stored procedure. In the same procedure, write an audit record to a table. Your audit table might track user name, execute time, execute duration, search criteria, etc. Hope this helps. -Marc

  • May 12, 2003 at 6:47 am

    #456818

    I have decided to use the trace procedures which offers maximum flexibility ( thanks Brian ) for my requirements.

    Thanks to all of you.

    Regards,

    --

    Diwakar Cirium

    --

  • May 12, 2003 at 8:14 am

    #456840

    I've set this up on a server I administer and it works like a charm. The overhead is pretty close to nonexistent. The only "catch" (in my scenario anyway - storing trace data to a file) is that you can't immediately view the trace data until you stop the trace to release the "hook" on the file. Food for thought: I set up jobs to stop the trace, rename the file and restart the trace automatically. "Helper" procedures can import the file data into a table and even summarize (!) the data.

    Have fun with it!

  • May 12, 2003 at 5:24 pm

    #456904

    Ken

    can you post the sql you are using if you can

    Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

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