?
Solved

Determine WHO deleted 5000 rows last wek

Posted on 1998-04-07
3
Medium Priority
?
329 Views
Last Modified: 2012-06-21
Someone (or a stored procedure) deleted all rows in a large table last week.  The create date did not change, and a trigger on the table inserted a recored of all 5000 rows being deleted.  Can the transaction log or anything else give more details/clues about how/who did this?
0
Comment
Question by:snydero
  • 2
3 Comments
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091117
You can't determine who did what from transaction log. You can try checking audit log to see at least who was logged in at the time, when delete happened. In the future you can use CURRENT_USER function in your tirigger to record the user name of person making changes.
0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091118
Unfortunatly there is no other way
0
 
LVL 2

Accepted Solution

by:
jbiswas earned 200 total points
ID: 1091119
There is no way to find this out, not even from the transaction log unless you have Microsoft look at it and they decrypt the info in the transaction log(which I doubt they will). They do have a way to decrypt the transaction log but like all other vendors they give noone the code to decrypt the tran log. This is a black box and noone with any sanity will give you a way to do this. However obviously the internal architechture can read this tran log, else how would they do replication from one server to another(Microsoft's replication schema is transaction based). However if you knew when the table got dropped, you could replay the transaction log till that point of time and recover from that disaster.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question