Query 1: “Hello friends, I am writing for the first time hoping that some of the learned friends can help me sort out the problem. Some of the tables of my SQL database are accessible to other users too. Yesterday, I noticed that certain rows got missing from the database table. How to find who deleted those rows from that table? Please help me with some genuine method.”
Query 2: “I received a query from a database user who wants to know “how to find who deleted records in SQL Server”. Here, I am talking about the Express edition of database. As per my assumption, this data can be found in the LDF file (if it has not been truncated). If I am correct, tell me how to find out this particular information from LDF file.”
If you want to know how to find out who deleted records from SQL Server, you may find these queries familiar. Hundreds of users like these are looking for ways to find out the culprit who deleted SQL data that caused them serious problem. If you are one of them, hold on, as we will tell you the most impressive method to locate who deleted SQL Server records.
How to Find Who Deleted Records in SQL Server
The record of the user who deleted some data from SQL Server can be found in the Transaction Log file. If you want to know, you need to retrieve that information from LDF file of your SQL database. For this, you will require SQL Transaction Log Viewer that can open and read LDF file data easily. Here is how you can read Log file data using this software.
a. Install and start SQL Log Analyzer Tool of your Windows system. Click on Open to add your log file.
b. A dialog box will appear that will showcase two different tabs for Online Database and Offline Database. Here we will show you the procedure of reading Log file form Online database. Select Online DB Options tab and then choose the Server name from the list. Also, select the Authentication mode and Select Database from the list. Click OK when done.
e. Now, you will see the complete LDF file data on the software screen. Any record can be previewed.
f. Users can sort the LDF file entries based on Transaction, Login Name, Time, table Name, and Transaction Name.
g. Just click on any table entry to select and preview the corresponding log entry of that operation. If you want to know who deleted a record, click on Delete transaction of that record. You will be able to see the Login Name of the person who deleted that data.
h. If you wish to export these data, check the boxes beside the necessary tables and click on Export.
j. Click on Export button when all the fields are filled properly.
k. The tool will export the selected data and upon finishing, prompt the users. Click OK.
l. The software will also display a report which you can save as CSV file for reference.
Note: Besides analyzing Log file data, this software can also retrieve deleted data if the database is in simple recovery mode.
SQL database often contains essential information and deletion of such data causes great inconvenience. Hence, we answered the question how to find who deleted records in SQL Server in this post. Using SQL Log Analyzer, users can open LDF file and find out who performed any particular transaction like Insert, Update, and Delete.