Find User who Executed DELETE on SQL Server Tables
Problem
Tracking of deleted data is a little bit tricky in SQL. In this article I will explain how to track down users who performed delete operation. We will examine this by using transaction Log.
Solution
Finding a user who executed DELETE statement
Step 1
To find out who deleted rows you need to query transaction log. Below mentioned query will fetch all records of transaction log.
SELECT
[Transaction ID],Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'
You can see all transactions returned in the above screenshot. As we are searching for deleted data in table Location, we can see this in the last row. We can find the table name in the "AllocUnitName" column.
Now capture the transaction ID we will use to trace.
Step 2
We found the transaction ID from the above command which we will use in the below command to get the transaction SID of the user who has deleted the data.
After finding transaction ID we can find SID of user who performed deletion operation.
SELECT
Operation,
[Transaction ID],[Begin Time], [Transaction Name],[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = '0000:000e29bc'
AND
[Operation] = 'LOP_BEGIN_XACT'
Here we can find very useful information e.g when transaction started, which can help us when data deleted. The above statement shows a delete statement executed on 2013/11/11 13:19:48:187 by user SID (0x010500000000000515000000FCE3153143170A32A837D665FFC90000)
Now our next step is to convert the transaction SID hexadecimal value into text to find the real name of the user.
We can convert SID into exact information which will show us the user who performed delete operation.
Step 3
Copy the hexadecimal value from SID column in previous result and pass this to SUSER_SNAME() function.
USE MASTER
GO
SELECT SUSER_SNAME(0x010500000000000515000000FCE3153143170A32A837D665FFC90000)
And here is the user who performed the Delete operation.
See Also
- [[Transact-SQL Portal]]