Find the user who has deleted records in a table

This might be the most common issue faced by lot of database developers. But I am going teach you very easy steps to catch those guys too quickly.
Be ready to HUNT 😛
Step 1:
Create a database to practice this case.
—————————————————
USE [master]
GO
— Create Database named DelUser.
CREATE DATABASE DelUser
GO
—————————————————
Step 2:
Create a table named FindDelUser.
—————————————————
USE [DelUser]
GO
— Create Table named FindDelUser.
CREATE TABLE FindDelUser (
[ID] INT PRIMARY KEY,
[Name] Varchar(50),
[Designation] Varchar(20)
)
—————————————————
Step 3:
Insert some records into the table.
—————————————————
USE [DelUser]
GO
— Insert data into table.
INSERT INTO [dbo].[FindDelUser]
SELECT 1, ‘Rob’, ‘CEO’
UNION ALL
SELECT 2, ‘Joy’, ‘Manager’
UNION ALL
SELECT 3, ‘Gary’, ‘Employee’
—————————————————
Step 4:
Delete few records from the table and check whether the records are deleted or not.
—————————————————
USE [DelUser]
GO
DELETE FROM [dbo].[FindDelUser]
WHERE [ID]=1
GO
—————————————————
USE [DelUser]
GO
SELECT [ID],[Name],[Designation]
FROM [dbo].[FindDelUser]
—————————————————
Step 5:
Fetch the deleted transactions from fn_dblog() function.
—————————————————
USE [DelUser]
GO
SELECT [Transaction ID], [Operation],
                [Context], [AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = ‘LOP_DELETE_ROWS’
—————————————————

Find-Deleted-User

Transaction_ID — Id of the transaction under which the records were modified.
Operation — Operation performed on the table.
AllocUnitName — Table name on which the operation was performed.

Step 6:
Copy the Transaction_ID in the above data and use the same in the below query to fetch the Transaction SID.
—————————————————
USE [DelUser]
GO
SELECT
[Operation], [Transaction ID],
[Begin Time], [Transaction Name],
[Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = ‘0000:000002ce’
AND [Operation] = ‘LOP_BEGIN_XACT’
—————————————————

Find-Deleted-User-1

Step 7:
Now just copy paste the above Transaction SID in the below and get the Username who deleted your records.
—————————————————
USE [master]
GO
SELECT SUSER_SNAME(0x010500000000000515000000D0688D7F2661AF2FF95D01242BFF0100) AS [UserName]
—————————————————

Find-Deleted-User-3

Note: We might get a doubt that how to get the deleted data back. There are two awesome features in SQL Server to do this. 

Happy Learning :)

Latest Comments
  1. Julian Castiblanco

    Useful!!!

    Thank you again.

Leave a Reply

Your email address will not be published. Required fields are marked *