Leveraging Storage Spaces Direct for SQL Server High Availability
Thursday, July 19, 2018 - click here to learn more
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 "
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 DB. USE [master]; GO CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO 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.
USE ReadingDBLog GO INSERT INTO Location DEFAULT VALUES ; GO 100
Now go ahead and delete some rows to check who has deleted your data.
USE ReadingDBLog GO DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO
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.
USE ReadingDBLog GO SELECT [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE 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.
USE ReadingDBLog GO SELECT Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:000004ce' AND [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.
USE MASTER GO SELECT SUSER_SNAME(0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000)
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.
USE ReadingDBLog GO 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.
USE ReadingDBLog GO SELECT Operation, [Transaction Id], [Transaction SID], [Transaction Name], [Begin Time], [SPID], Description FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ' GO
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.
Once again, we found the user in question.
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: 2013-12-02
About the author
View all my tips