Solved

Determine WHO deleted 5000 rows last wek

Posted on 1998-04-07
3
285 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: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:spiridonov
ID: 1091118
Unfortunatly there is no other way
0
 
LVL 2

Accepted Solution

by:
jbiswas earned 100 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

770 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