Someone has dropped a table from your database and you want to track who did it. Or someone has deleted some data from a table, but no one will say who did. In this tip, we will look at how you can use the transaction log to track down some of this information.
I have already discussed how to read the transaction log file in my last tip "How to read SQL Server Database Log file". Before reading this tip, I recommend that you read the previous tip to understand how the transaction log file logs all database activity.
Here we will use the same undocumented function "fn_dblog" to find any unauthorized or unapproved deletes or table drops. This tip will help you track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. I strongly suggest testing any undocumented functions in a lab environment first.
One way to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on your instance, but if you have a busy system the trace files may roll over far too fast and you may not be able to catch some of the changes in your database. But these changes are also tracked in the transaction log file of the database and we will use this to find the users in question.
Finding a user who ran a DELETE statement
Before moving ahead, we will create a database and a table on which I will delete some data. Run the below SQL code to create a database and table.
CREATE DATABASE ReadingDBLog;
-- Create tables.
CREATE TABLE [Location] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'Bangalore');
We have created a database named "ReadingDBLog" and a table 'Location' with three columns. Now we will insert a 100 rows into the table.
INSERT INTO Location DEFAULT VALUES ;
Now go ahead and delete some rows to check who has deleted your data.
DELETE Location WHERE [Sr.No]=10
SELECT * FROM Location WHERE [Sr.No]=10
You can see in the above screenshot that a row has been deleted from the table "Location". I also ran a SELECT statement to verify the data has been deleted.
Now we have to search the transaction log file to find the info about the deleted rows. Run the below command to get info about all deleted transactions.
Operation = 'LOP_DELETE_ROWS'
All transactions which have executed a DELETE statement will display by running the above command and we can see this 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. The last row says a DELETE statement has been performed on a HEAP table 'dbo.Location' under transaction ID 0000:000004ce. Now capture the transaction ID from here for our next command.
We found the transaction ID from the above command which we will use in the below command to get the transaction SID of the user who has deleted the data.
[Transaction ID] = '0000:000004ce'
[Operation] = 'LOP_BEGIN_XACT'
Here, we can see the [Begin Time] of this transaction which will also help filter out the possibilities in finding the exact info like when the data was deleted and then you can filter on the base of begin time when that command was executed.
We can read the above output as "A DELETE statement began at 2013/10/14 12:55:17:630 under transaction ID 0000:000004ce by user transaction SID 0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000.
Now our next step is to convert the transaction SID hexadecimal value into text to find the real name of the user.
Now we will figure out who ran the DELETE command. We will copy the hexadecimal value from the transaction SID column for the DELETE transaction and then pass that value into the SUSER_SNAME () function.
Now we have found the user that did the delete.
Finding a user who ran a DROP statement
Here I am going to drop table Location.
DROP TABLE Location
Similarly if you drop any object or you perform anything operation in your database it will get logged in the transaction log file which will be visible by using this function fn_dblog.
Run the below script to display all logs which have been logged under DROPOBJ statement.
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
Here we can find the transaction SID and all required info which we need to find the user.
Now we can pass the transaction SID into system function SUSER_SNAME () to get the exact user name.
Use this function to do more research into your transaction log file. There is a lot of informative data in more than 100 columns when you use this command. You may also need to look into this and correlate with other data. Explore more knowledge on SQL Server Database Administration Tips.
Last Update: 12/2/2013
About the author
Manvendra Signh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.
Very Nice and informative post!! Thanks for bloging .
I have a doubt , if the log file gets purged , can we still get the information . Their is another techique by running a report from ssms about the schema change history ,whih is tracked from default trace.
Using an unsupported feature as a default go to tool is not a great idea especially as Microsoft might withdraw it at any time. Ok trace is going but still available and supported. Plus on log truncation you won't be able to use fn_dblog() and wo betide the DBA who runs unsupported features on production and something goes wrong. There is another function with which you can read archived log files however I won't provide any details due to the side effects, which I shall similarly not describe. These features should not be used in production. While fn_dblog() is now well documentedd on the internet this is not a feature you should use without fully understanding the potential consequences. As most people don't I'd rather this article were about using extended events to track this sort of information. Dropping user tables should not happen so often that an XE would cause you any issues.
nice info for people who didn't know but I think it mildly scary how people fling functions like this about as a norm.
Monday, December 02, 2013 - 12:26:10 PM - Srinivas
Really cool article, until you realize that it exists in other places on the web, most notably from the master himself : Paul Randall.
Very good tools for every DBA to have, but, be cognizant of the simple fact that they're dangerous. For example, if you use them in your production environment, and something goes awry, you're on your own, you can forget calling Microsoft.
Hi, I have one more quick question is there a way we can find out from which machine/IP address the user accessed and deleted the records?. In our case the client gave only one user but 5 different users are accessing the database from different machine. It would be great help if someone can let me know the answer for this. Thanks, JK.