Get a User that has Deleted Rows with SQL

Getting a user that has deleted rows with SQL is a multi-part process.

First you have to search the transaction log file for information on deleted rows.

SELECT     [Transaction ID],    Operation,    Context,    AllocUnitName    FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS' --Get Deleted Rows & Transaction ID

This will show you the Deleted Rows and give a Transaction ID for each operation. Now you need to use this Transaction ID to get the transaction SID of the user who has deleted the data:

SELECT     [Transaction ID],    Operation,    Context,    AllocUnitName    FROM     fn_dblog(NULL, NULL) WHERE 		[Transaction ID] = '0000:0000035' --Example Transaction ID to Delete	AND	    Operation = 'LOP_BEGIN_XACT' --GET Transaction SID

Finally, you need to convert the Transaction SID into readable text to display the user details:

SELECT SUSER_SNAME(0x0105000000000002120000009F11CA398C81F81398D0CD14E8030030) --Based on Transaction SID That Was Returned Earlier
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: