Post reply

Urgent

  • August 23, 2007 at 8:46 am

    #120180

    Please help...

    Someone have deleted a table in sql server 2k5 express.HOw can we find the user who have done that.

    Thanks In ADVANCE...

  • August 23, 2007 at 9:09 am

    #728998

    If you had some kind of DDL auditing running - DDL triggers, Profiler, etc., or you have a transaction log reader tool like Lumiget Log Explorer, you could find out who dropped the table.  If you don't have any of those tools set up, you won't be able to find out.

    Greg

    Greg

  • August 23, 2007 at 9:11 am

    #729000

    More likely than not, you can't.

    Unless you have some sort of auditing set up it's highly unlikely you'll be able to find out who issued the delete statement. I'd look at the list of users who have access to do something like that and then start narrowing it down from there.

  • August 23, 2007 at 10:52 am

    #729039

    The transaction log does not always record the user who is responsible for a particular action, but I'd do something like:

    dbcc log(databaseid,-1) with tableresults

    You need to fill in the database id. If you do this quickly, before the log is reused, you may be able to get the userid of the person who did it.

    You will need to have motivation to understand what the result is, it contains a lot of info.

    (I would not like to feel bad about giving this advice, are you planning to fire that person? If so, just do not tell me.)

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 23, 2007 at 11:28 am

    #729049

    Hi Andras,

    I also ran this command out of curiousity and in order to preserve it for future emergencies. However it displays log record in binary format. Do you have a script that would convert it to the actual wording ?

    Thanks

  • August 23, 2007 at 12:03 pm

    #729064

    Hi Mark,

    For that you would actually need a proper log reading tool. (I've written one a few years ago, but it is only for SQL Server 2000. This of course makes it easier for me to understand the binary part. There are other log readers for 2005.). Also, the above method does show you only the active transaction log and a bit more. You could get various information out by changing the second parameter to 1, 2, or 3

    In this case, if one just wants to find the user to blame for a table drop, and can identify the transaction, the transaction start log record (most of the time) contains the userid.

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 23, 2007 at 9:24 pm

    #729154

    SQL 2005 has a default trace running in the background; you can find the trace in SQL logs directory. Look for event 46 or 146 (i think ) which means dropping an object; look for the object name under the object column to confirm your finidings. The user name and logins should be listed also in the trace.

    A quicker way; if you're running SP2; right click the SQL instance, reports, run the schema change history report and you'll get the same information and more in much organized way.

    Ayman

    Welcome to the dark side of the DBA job

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

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