Post reply

Someone dropped a table...not good

  • July 27, 2006 at 7:50 am

    #60675

    I am supproting a server that houses a third party vendor app. Someone apparently dropped a table in the db and I want to see who did this. Can I read the transaction log in order to see this? Any good tools out there to do this?

  • July 27, 2006 at 8:01 am

    #651886

    Hi,

    Your best bet will be something like Lumigent's Log Explorer or SQL Log Rescue by Red Gate.  There are similar products out there on the market.

    All of these products depend on you either having log backups or having an untouched log.  If you're running in simple recovery mode or the log was truncated you've got no hope of finding out who did it short of someone owning up to it.

    Hope that helps.  Good luck,

    Karl
    source control for SQL Server

  • July 27, 2006 at 8:07 am

    #651887

    If auditing is enabled on the server then, you can find the id from there as well.

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • July 27, 2006 at 8:16 am

    #651890

    Forgive my ignorance but are you talking about SQL auditing? How do I turn that on? Where is the information stored?

  • July 27, 2006 at 8:25 am

    #651891

    I believe Prakash is talking about C2 auditing, which can be enabled using sp_configure.  I'd check it out in BOL first and I'd advise caution before using it because it audits absolutely everything and can quickly fill up your drives and adversely affect performance.

    And of course, it won't do anything to help you figure out who dropped the table (unless you already had it turned on).

    Moving into the future, rather than enabling auditing, I would suggest that you limit permissions so that only a select few people can run DDL statements like DROP TABLE.  And in third-party databases, no one but the DBA(s) should have that kind of level of permissions.  At least, that way, you can be certain that it must have been one of a few people.  Preventing it from being able to happen in the first place is a better strategy than having to play detective once the crime has happened.

    Karl
    source control for SQL Server

  • July 27, 2006 at 9:38 am

    #651918

    Yes i was refering to C2 auditing only.

    thanks Karl for explaning.

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

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

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