Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















How to find user who ran DROP or DELETE statements on your SQL Server Objects

MSSQLTips author Manvendra Singh By:   |   Read Comments (31)   |   Related Tips: More > Database Administration
Problem

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.

Solution

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

Step 1

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');

Step 2

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

Step 3

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
Delete a row from the table'location'

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.

Step 4

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'

Find all the deleted rows info from t-log file

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.

Step 5

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'

Find the transaction SID of the user

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.

Step 6

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)

Find the login name with the help of transaction SID

Now we have found the user that did the delete.

Finding a user who ran a DROP statement

Step 1

Here I am going to drop table Location.

USE ReadingDBLog
GO
DROP TABLE Location

Drop a table

Step 2

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

Finding a user trasaction SID who ran DROP statement for table location

Here we can find the transaction SID and all required info which we need to find the user.

Step 3

Now we can pass the transaction SID into system function SUSER_SNAME () to get the exact user name.

SELECT SUSER_SNAME(0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000) 

Finding a user who ran DROP statement for table location

Once again, we found the user in question.

Next Step

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
MSSQLTips author Manvendra Singh
Manvendra Signh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, December 02, 2013 - 1:38:53 AM - Vaibhav Read The Tip

Hello Manvendra,

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.

 

Thanks,

 


Monday, December 02, 2013 - 10:57:58 AM - Olu Read The Tip

Brilliant! I read this tip and the previous one (How to read SQL Server Database Log file). I learnt so much from them. Thanks so much.


Monday, December 02, 2013 - 11:57:53 AM - RUnwin Read The Tip

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 Read The Tip

 

Hey,

 

Very nice article....and helpful


Monday, December 02, 2013 - 1:09:30 PM - John Read The Tip

 

Hi 

 

In an instance, if the username is a process id, can we find out from which machine/terminal this command was issued.

Please advise


Monday, December 02, 2013 - 1:14:39 PM - John L Read The Tip

Well done!


Monday, December 02, 2013 - 3:20:31 PM - PhyData DBA Read The Tip

Why not use the SCHEMA update report available in SSMS since SQL 2005?


Monday, December 02, 2013 - 10:12:53 PM - Chandu Read The Tip

Very Informative, Thanx for sharing.


Tuesday, December 03, 2013 - 12:35:32 AM - Wasim Read The Tip

This is not working in the following cases:

1. After delete the database is backed up and restored again on another server.

2. When Recovery Model is SIMPLE and Delete is taken place 2-3 days before.

Can the Delete OR Drop be identified in the above cases?

If Yes, then can someone let me know the way to recover the same.

 


Tuesday, December 03, 2013 - 3:53:22 AM - shravan Read The Tip

Thanks for providing informative Article.

One question is : If  I want to know from which computer the Delete/Drop query issued. Is it possible ???


Tuesday, December 03, 2013 - 8:15:56 AM - Vikram Mahapatra Read The Tip

fantastic, code is enough to describe the objective of the article... nice article. 


Tuesday, December 03, 2013 - 9:11:47 AM - Deepak Read The Tip

nice article


Tuesday, December 03, 2013 - 12:28:40 PM - dyako Read The Tip

Thanks,very much

 

 

 


Tuesday, December 03, 2013 - 9:11:17 PM - Gopalakrishnan Read The Tip

Excellent Article.......


Saturday, December 07, 2013 - 11:43:58 PM - Golam Read The Tip

Very clear and well presented - thanks and pls keep posting

 


Saturday, December 07, 2013 - 11:49:50 PM - Gaurav Srivastava Read The Tip

 

Thankyou 

Nice Article and very Informative

 

thanks once again

Gaurav Srivastava


Thursday, December 12, 2013 - 9:51:28 AM - Salem Read The Tip

Awsome, tons of thanks


Thursday, December 12, 2013 - 11:08:13 AM - EHolland Read The Tip

Manvendra,

Thank you for an Awesome article.

One question, for Step 5 in the Finding a user who ran a DELETE statement; shouldn't it have been filtered (in the WHERE clause)
on the following:

WHERE
         [Operation] = 'LOP_BEGIN_XACT'
AND
         [Transaction Name] = 'DELETE'

Thanks again.


Sunday, December 15, 2013 - 9:38:16 PM - Mohamed Read The Tip

Good


Tuesday, December 17, 2013 - 1:29:37 PM - jamal Read The Tip

NICE ARTICLW


Tuesday, December 17, 2013 - 1:30:11 PM - jamal Read The Tip

nice


Wednesday, December 18, 2013 - 9:46:39 AM - Pramod Tiwari Read The Tip

Thanx


Thursday, January 02, 2014 - 5:52:48 PM - Jyo Read The Tip

Very nice post. Is it possible to go one level down to see which column data was deleted by who? Thank you in advance.


Monday, January 20, 2014 - 10:00:50 AM - Fadhl AL-Sharee Read The Tip

thankyou..


Thursday, January 30, 2014 - 5:48:07 AM - S Paruchuri Read The Tip

Very useful article. Thanks.


Thursday, January 30, 2014 - 7:29:08 AM - gaurav kaushik Read The Tip

great man....


Monday, February 10, 2014 - 10:55:36 AM - Winston Daley Read The Tip

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.

 

http://www.sqlskills.com/blogs/paul/category/undocumented-commands/

 


Monday, February 10, 2014 - 1:43:18 PM - Karthikeyan Jothi Read The Tip

 

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.

 


Monday, March 03, 2014 - 6:12:47 AM - Ramesh M Read The Tip

 

Nice Article... IS there any way to find the who ran update on the table.


Friday, March 14, 2014 - 2:12:40 AM - rakesh Read The Tip

It is really good. But i also want to know about the user who ran truncate query. Can U help me.


Monday, April 21, 2014 - 4:48:51 AM - Adinarayana Read The Tip

Excellent Article.

 

Can we check how much data have been deleted in the delete operation.

can we recover that deleted data?

 

Thanks,

Adi

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.