Check Who Deleted Data from Table in SQL Server – Step-by-Step

When it comes to data safety, it becomes crucial for the database administrators to be aware of the ways to check who deleted data from table in SQL Server. However, due to several reason, it often becomes complex for the users to track the deleted records. With this technical write-up, we will discuss not only the challenges with the deleted data tracking, but we will also suggest the best ways that can help with finding out who deleted the data from SQL Server database. To understand the issue better, let’s first take a look at a user query for the same scenario.

Problem
I was fetching few records from my database tables and what I saw, there is a huge gap of 157 records between rows with letter ‘M’. So, I contacted back-end support team to find out who deleted data from the table in SQL Server? But, unfortunately, I was unable to find the person, and nobody is taking responsibility for the deleted records. Is there any way to find out who deleted those records from SQL Server?

Solution

After some initial research, we found that the Transaction LOG file of a SQL Server database is very helpful to find out the activities of database users. But, the main problem is, one cannot easily view log file of SQL Server directly. For this, you need to have knowledge of the undocumented fn_dblog() function.

Run the Below command for FN_DBLOG to check who deleted data from table in SQL Server:

Select [Transaction SID],
[Transaction Name] from fn_dblog(NULL,NULL)
WHERE [Transaction Name]= ‘DELETE’
Go

This fn_dblog find deleted records method is not that accurate & does not work in every situation. Therefore, proceed with the detailed solution..

Why is it Challenging to Check Who Deleted Data from Table in SQL Server?

• One of the reasons that makes it difficult is that SQL Server doesn’t track who deleted or truncated records in the database. 
• The next cause that results in a challenge is that the transaction logs in SQL Server are designed to record the changes made in the SQL Server table data, but they are not capable of tracking who deleted the data within the database. 
• When a user runs the DELETE operation, they can do it either directly or indirectly, like using a stored procedure or with the help of a service account. This leads to the possibility that the name of the user or the account isn’t tied to the command.
• Even if there are permission restrictions in the SQL Server database, that doesn’t mean the commands and operations will be recorded making it difficult to check who deleted data from table in SQL Server. The security metadata can only help with detecting who has the authority to delete data, but not who deleted the data.
• In case a user restores SQL database using backups, it will also only help with learning which data was deleted by comparing both data. But it will still not display who deleted the data.

These are some of the reasons that make it challenging for users to track who deleted the data from the table. We will now take a look at the methods that will help with how to check who deleted table in SQL Server in a quick and hassle-free way.

How to Find Out Who Deleted Data from Table in SQL Server?

Now, to check who deleted data from table in SQL Server, it’s time that we proceed with an example. Therefore, below, we’re going to create a database, add values, delete values, & then execute the SQL query to find deleted records in SQL Server along with the user name.

Therefore, the very first task for us is to create a dummy database. The command for the same is present below:

Step -1. Create a database to begin.

-- Create Database–
USE [master];
GO
CREATE DATABASE Test_Database;
GO
-- Create tables.
USE Test_Database;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore');

Step-2. Add values to the database table to proceed with the process to check who deleted data from table in SQL Server.

Once the database is created, users need to add data in it. As we already have more than 2 columns, we are going to add values of around 100 rows here. The command for the same is:

USE Test_Database
GO
INSERT INTO Location DEFAULT VALUES ;
GO 100

Step-3. Deleting Data from the table.

Once we are done with adding the values, it’s time that we proceed & delete some rows to make a situation similar to the users’ misery.

USE Test_Database
GO
DELETE Location WHERE [Sr.No]=10
GO
SELECT * FROM Location WHERE [Sr.No]=10
GO

The reason we also used the Select command is to cross-verify if the data exists or not.

In case the data has been accidentally deleted from SQL Server database and the database administrators need to recover that data, it is always beneficial to go with a professional solution. Using a tool like SQL Database Recovery Tool, allows users to recover any deleted data from the SQL Server database files, and further scans for any corruption in the database. To ensure a safe recovery, the software is designed to carry out the entire process while preserving data integrity.

Step-4. Now, it’s time to use a SQL query to find deleted records in SQL Server from the transaction log file in order to collect the deleted rows information. To get this data, we need run the following command with fn_dblog find deleted records: 

USE Test_Database
GO
SELECT
   [Transaction ID],
   Operation,
   Context,
   AllocUnitName    

FROM
   fn_dblog(NULL, NULL) 
WHERE
    Operation = ‘LOP_DELETE_ROWS’

Now, users can see that here, we got the transaction ID. This ID will help us further find user who deleted data.

Step-5. Our next task is to find out the Transaction SID. To find this, we need to execute the command below & put the Transaction ID we received in the previous command.

USE Test_Database
GO
SELECT
   Operation,
   [Transaction ID],
   [Begin Time],
   [Transaction Name],
   [Transaction SID]
FROM
   fn_dblog(NULL, NULL)
WHERE
   [Transaction ID] = '0000:000004ce'
AND
    [Operation] = 'LOP_BEGIN_XACT'

Here, we can see that we are successful in finding the SID. Now, with the help of this SID, it’s very easy to learn how to find who deleted records in SQL Server DB.

Step-6. By executing the given SQL query to find deleted records in SQL Server, with the SID, we can see the user who executed the Delete or Drop command.

USE MASTER
GO  
SELECT SUSER_SNAME(0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000)

What if SQL Server Connection is Close?

The fn_dblog find deleted records function is helpful to track the user who have performed certain transactions in SQL Server. But, once the SQL Server connection closed, it will not display the DML operations until you perform new transactions. In such situation, it is very difficult to find the user Who deleted data from table in SQL Server. So, we suggest using the second option i.e. SysTools SQL Log Analyzer Tool to track who is responsible for deleting certain records in SQL Server. This advanced utility has a functionality to explore the complete details of SQL Server Transaction log file. You need to provide either the Server detail or simply load an offline LDF file along with its associated MDF file into the suggested software.

Download Now Purchase Now

Related : Recover Data from Log file in SQL Server – A Complete Guide

Track Who Deleted Data from Table using Transaction Log Reader

With the help of the advance utility, database administrators can easily check who run the DELETE operation. This allows them to keep the database safe from future risks of losing data or even any threats from insiders within the organization. You can also check out the video of this software for the ease.

Follow the steps below to Track the Username

Step 1: Download and Install the software in your Windows machine.

Step 2: After installing the utility, launch the software. On the welcome screen, click on Open to provide server details like Server Name and Authentication type. This will help with resolving the issue.

Step 3: Now, select the database, to which user activities you want to track. Click on OK button.

Step 4: Scan process will start and display all the transactions (Insert, Update & Delete) performed on the database tables. Once the scan completes, it will provide a preview.

Step 5: Preview the recovered data in the software panel. This will easily help to find out who run the DELETE command in the database.

Step 6: You will be able to see the Login Name column, which is the user who performed certain transactions on that database tables.

Step 7: Next, software will provide the export option to save the transactions directly to SQL Server Database, SQL Server Compatible Script or CSV format.

Also Read: Fix SQL Server Error 825 With The Best Ways Possible

The above steps will help you to track, who deleted data from table in SQL Server even if the connection has been closed in SQL Server.

Final Say!!

Tracking the transactional activities of a user by using fn_dblog() function is easy, until the SQL Server connection is open by the user. But, once the connection closed, SQL Server find who deleted records become a complex task to find the DML operations using fn_dblog find deleted records. Therefore, we have explained the automated solution to learn how to find who deleted data from table in SQL Server.