Find the user who executed DELETE on tables in SQL Server
Introduction
To find out the user, who deleted one or more rows from a table we need to query transaction log. The below-mentioned query will fetch all records of the transaction log. Run the below script in that database where the rows were deleted.
Find transaction ID
|
1 2 3 4 5 |
Use AdventureWorks2017 -- Database Name SELECT[Transaction ID],Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS' GO |
We 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. Here Person.Person is the table name.
Find transaction SID
We found the ‘transaction ID’ from the above result. Now we will use that to find ‘transaction SID’ of the user who has deleted the data. Copy ‘transaction ID’ from the above result & run the below script.
|
1 2 3 4 5 6 7 8 |
Use AdventureWorks2017 -- Database Name SELECT Operation, [Transaction ID],[Begin Time], [Transaction Name],[Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:000015d1' -- Past transaction ID here AND [Operation] = 'LOP_BEGIN_XACT' GO |
In the above result, we got ‘transaction SID’.
Convert SID into User
Now our next step is to convert the ‘transaction SID’ hexadecimal value into the 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. Copy the hexadecimal value from SID column in the above result and past it to SUSER_SNAME() function & run the below script in the master database.
|
1 2 3 4 |
USE MASTER GO SELECT SUSER_SNAME(0x01050000000000051500000003B7FE1924F592E6C4053B9FE9030000) -- Put SID here GO |
Result
Finally, we found the user who executed DELETE on SQL server tables & deleted the row.
Source: https://social.technet.microsoft.com