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.
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.
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
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
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.
Tracking the transactional activities of a user by using fn_dblog() function is easy, until the SQL Server connection is not closed 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 SysTools SQL Log Analyzer to check who deleted data from table in SQL Server.