devxlogo

Get a User that has Deleted Rows with SQL

Get a User that has Deleted Rows with SQL

Why Trust Us

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
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More