Check Who Deleted Data from Table in SQL Server – Step-by-Step
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 that who deleted data from table in SQL Server? But, unfortunately I was unable to find the person and nobody is taking the responsibility for 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 undocumented fn_dblog() function.
Run the Below command for FN_DBLOG find deleted records task:
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 for the detailed solution..
Table of Content
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.
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 is deleted or not.
Step-4. Now, it’s time to search the transaction log file in order to collect the deleted rows information. To get this data, we need run the following command:
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 in finding the user who deleted data from table SQL Server database.
Step-5. Our next task is to find out the Transaction SID. To find this, we need to execute the below command & 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 command 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 has been Closed?
The fn_dblog() 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, it is advised to use the second option i.e. SysTools SQL Log Analyzer to track who deleted certain records in SQL Server.
Related : Recover Data from Log file in SQL Server – A Complete Guide
Track Who Deleted Data from Table using Transaction Log Reader
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 SQL Log Analyzer Software.
Follow the Below Steps to Track the Username
- Download and Install the software in your Windows machine.
- After installing the utility, launch the software. On the welcome screen, click on Open to provide server details like Server Name and Authentication type or load .ldf file using offline option.
- Now, Select the database, to which user activities you want to track. Click on OK button.
- Scan process will start and display all the transactions (Insert, Update & Delete) performed on that database tables.
- After scanning the database transactions, It will display the transactional activity in preview panel.
- You will be able to see the Login Name column, which is the user who performed certain transactions on that database tables.
- Next, software will provide the export option to save the transactions direct 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. You can also check out the video of this software for the ease.
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() function. Therefore, we have explained the automated solution to learn how to find who deleted data from table in SQL Server.