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

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.

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.

Result

Finally, we found the user who executed DELETE on SQL server tables & deleted the row.

Source: https://social.technet.microsoft.com