Post reply

Monitoring user activities in sql server

  • February 22, 2005 at 8:08 am

    #87722

    i have been having a problem with data being deleted from a critical table, by users coming in via a third party application.

    how do i set up a trace or a quesry to determinde the user that is doing this?

    thanks

    [font="Comic Sans MS"][/font]It is what it is.

  • February 22, 2005 at 8:26 am

    #543479

    Hey,

    Couple of ways...

    1)  Profiler trace and filter on ObjectName.  This will show you all activity on the table.

    2)  Create a trigger on the table to capture deleted rows and do a lookup on the user name.  Obviously, if this is a 3rd party tool adding a table and a trigger may invalidate any support you have, so you should check with the vendor if you can do this first.

    Hope that gets you on the right track.

  • February 22, 2005 at 8:45 am

    #543488

    I agree with Clice. He's got the two best ways to accomplish this. Profiler is less invasive (monitoring only), though you could build a trigger to just insert logging informaiton into some table.

    I'd talk it over with your vendor's support person and let them know what you'd like to do.

  • February 22, 2005 at 11:09 am

    #543536

    Thanks a bunch

    [font="Comic Sans MS"][/font]It is what it is.

  • February 22, 2005 at 12:33 pm

    #543543

    Be wary, as running profiler on a server can impose a performance overhead. As long as you are not doing a huge number of deletes, I would recommend catching the username inside of a trigger.

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

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