none
SQL Server Profiler???

    Question

  • Hi guys, 

    Quick question.  Can SQL Server Profiler help you find what user ran what queries against a table?  Can it help you find who deleted data from a table?

    Friday, August 23, 2013 1:53 PM

Answers

All replies

  • In general, yes.  But not after the fact.

    Friday, August 23, 2013 1:57 PM
  • Yea, I mean after the fact.  If profiler is not the tool for that sort of job, then what can I use to see who did what to a table?  
    Friday, August 23, 2013 2:14 PM
  • you can't, you have to have something in place before it happens, such as SQL Server audit, profiler, trace, etc.

    Thanks, Andrew

    Friday, August 23, 2013 2:27 PM
  • Is it common practice to always have a trace running to pick up that type of information?
    Friday, August 23, 2013 4:39 PM
  • IMO no.  Generally speaking, it is more effective to prevent people from doing things they should not be doing then it is to repair the damage afterwards.  Triggers and auditing are not the best solution to a training / management issue.
    Friday, August 23, 2013 7:14 PM
  • No.  If you want to capture this kind of information, you should build something like an audit trail into your application or database.


    Friday, August 23, 2013 7:43 PM
    Moderator
  • Is it common practice to always have a trace running to pick up that type of information?

    I like to run a trace - and perfmon - 24x7 to record usage patterns and problems, BUT neither of these nor any other technical tools are going to be fine-grained enough tell you every detail of who did what to whom.  For example I usually record only those transactions that take over 2 seconds to run, but you can drop a table or delete a lot of data in less time than that.

    There may be more app-centric audit tools that would, but if you try to keep that level of detail ANY tool is going to start putting a LOT of overhead on your system and accumulate gigabytes and terabytes of log information in short order.

    Some non-technical manager types will say, "Do it anyway!", until they see the actual cost.  It might even be practical on smaller systems, at fairly high cost.  But on major systems you're already using all the capacity just to keep rolling, so a comprehensive audit trail just ain't happening.

    Finally, profiler may or may not tell you who did something if they do it through an application that uses a connection pool, you may only know that someone did it through the app, but not who.

    Josh

    Saturday, August 24, 2013 4:54 PM