Urgent

Question

Urgent

VastSQL

SSC-Dedicated

Points: 36121
More actions
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...

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

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.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

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

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

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

Ayman SSChasing Mays Points: 637 More actions · Answer 6

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