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.
How to Find Out Who Deleted Data from Table in SQL Server?
To check who deleted data from table in SQL Server, you first need to run the below command in SQL server.
Select [Transaction SID],
[Transaction Name] from fn_dblog(NULL,NULL)
WHERE [Transaction Name]= ‘DELETE’
Go
Thus, you will be able to find the [Transaction SID] of deleted transactions that were performed by a particular user.
Now, to get the complete detail of that user, you can pass [Transaction SID] within suser_sname() function.
After executing the above command, you will be able to find out who deleted data from table in SQL Server.
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 Rescue 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.
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, it will 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.