Post reply

How To Capture ALL Data Retrieval Actions Regardless of Method

  • February 5, 2014 at 11:04 am

    #282014

    How to construct a SQL Profiler Trace That Will Capture all Data Retrieval actions from a specified SQL Login

    I need to construct a Trace that will capture all actions to retrieve data from a specific database by a specific SQL Login. I don’t know what data retrieval methods the login may use only what DB they are connecting to and what SQL Login they will use.

    The user may execute a vaniall SELECT or they may call a Stored Procedure that retrieves the data, they may even choose to save as XML. INo matter how they retrieve the data I need to capture n a trace their action.

    Is that possible in a trace (either via Profiler or a server side trace) and if yes then what events do I need to include in the trace? My concern is that the standard events to capture standard SELECT statements may not capture other data retrieval methods like the use of a Stored Procedure or if they choose to save as XML. I am going to be tracing the actions a specific login and I need to ensure I capture every data retrieval action.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • February 5, 2014 at 12:54 pm

    #1686339

    Well, you can use Profiler to set up the events and filters you want to monitor. I would suggest:

    Stored Procedures -> RPC:Completed

    TSQL -> SQL:BatchCompleted

    You may want others, so play around a bit. Once you have the trace to your liking, you can script it out. In the File menu, select "Export", "Script Trace Definition". You may need to do a little work to configure an output file. After that, you can quietly run this trace in the background. The bigger questions will eventually become how often will someone go and look at the output of the trace, and how will you clean up the files that get output.

  • February 5, 2014 at 1:50 pm

    #1686354

    YSL i just created one and scripted it out for you.

    This example is filtering for a specific databaseID (18) , for a specific user, and ignoring any '%exec sp_reset_connection%' commands that i had seen filling my trace unnecessarily with irrelevant rows.

    it's a space hog, with rollover files that total 500Meg.

    at least scripted out, it's easy to review and modify.

    this is a slight modification of one i have running during production hours, that i use to harvest slow running queries for subsequent

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 18, 2014 at 2:51 pm

    #1690063

    Lowel,

    Thanks for the script.

    Kindest Regards,

    Just say No to Facebook!

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

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