In the world of SQL Server administration, it’s not uncommon to encounter situations where important data mysteriously disappears from tables. As a database administrator (DBA), it’s crucial to identify the user responsible for deleting the data. This helps ensure data integrity, maintain security, and take appropriate actions when needed.
Finding the person behind a DELETE operation may seem challenging, but don’t worry! SQL Server provides powerful tools and techniques that allow DBAs to investigate transaction logs and uncover the truth about data deletions.
In this article, we’ll go on a journey to discover the culprit behind DELETE operations in SQL Server. We’ll explore the transaction log, which is like a detailed record book of every action performed on the database. By using the transaction log and applying smart querying methods, we’ll reveal the identity of the user who deleted the data.
Throughout this post, we’ll provide step-by-step instructions, best practices, and useful tips to guide you in finding the user who executed DELETE statements. By the end, you’ll have the knowledge and tools necessary to quickly pinpoint the responsible user. This will enable you to take appropriate actions and prevent similar incidents in the future.
So, let’s dive into the world of SQL Server transaction logs and uncover the person responsible for those mysterious data deletions. Get ready to become a database detective and master the art of investigation in database administration!
In order to identify the user responsible for deleting one or more rows from a table, you can query the transaction log. The query provided below will retrieve all the records from the transaction log. Please execute the following script in the respective database where the deletion occurred:
Step:1 Find transaction ID
USE AdventureWorks2017; -- Specify the database to use -- Retrieve transaction log records for 'LOP_DELETE_ROWS' operation SELECT [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS';
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 ‘PersonAddress‘ is the table name.
Step:2 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.
Use AdventureWorks2017 -- Database Name SELECT Operation, [Transaction ID],[Begin Time], [Transaction Name],[Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:0000163f' -- Past transaction ID here AND [Operation] = 'LOP_BEGIN_XACT' GO
In the above result, we got ‘transaction SID‘.
Step:3 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.
USE MASTER GO SELECT SUSER_SNAME(0x010500000000000515000000C61DED7F84C6AB4A64B2E9DDE9030000) -- Put SID here GO
Finally, we found the user who executed DELETE on SQL server tables & deleted the row.