Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (48)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, July 08, 2017 - 12:42:08 PM - Charaka Dharma Back To Top

Still learning new stuff every day!


Thursday, June 01, 2017 - 11:41:18 AM - Megha Back To Top

For me it is showing NULL in column name I did not user name who deleted.

 

SELECT suser_name (0x010500000000000515000000BB0ADA1175B9755443170A32ED390000)

 


Tuesday, February 28, 2017 - 8:10:29 AM - JC Back To Top

 Excellent article, thank you very much for sharing the knowledge.

 


Sunday, September 11, 2016 - 2:17:27 PM - anjaneytulu Back To Top

 HI Manvendra ,

very useful article. Thanks.


Tuesday, May 17, 2016 - 7:54:41 PM - Lakshmi Back To Top

Issue : one job which runs for every 5 minutes failed today (completed successfully yesterday) with error one of the  object database.dbo.Table was not found.

 

The database was not found on the server. Immediately Application team  created the DB to resolve the issue and then the job started succeeding

 

I am troubleshooting the reason

 

Checks:

 

1.                   Checked the server log and error log both current and archived .No information was available expect  the latest create database

2.                   I checked the job modified date and created date both are of last month

3.                   I checked the SP which is running under this job. The last modified date of this SP is also last month

4.                   daily running maintenance jobs are not considering this database(found this in the log file of maintenance job)

5.                   Checked for any hardware failures, some other databases were also existing on the same drive and no issues with these

6.                   No related errors in the event viewer too

 

I thought  the database was never existed ,then how come the job completed successfully yesterday?

 

 

Am I missing something else?


Friday, April 22, 2016 - 4:48:51 AM - Sammy Machethe Back To Top

On point. Thanks for sharing.


Thursday, June 11, 2015 - 1:19:46 AM - Amos Back To Top

Hello Manvendra,

     I've found this articke Very Useful, I apply this tip

 

    I think than in order to get the name of the user who has performed one of the above activities,

    you can write, for example the single statements for deletion,  in one query as follows:


select first.[Transaction ID],
          first.Context,
         second.[Begin Time] as [Deletion time],
         second.[Transaction Name],
         SUSER_SNAME(second.[Transaction SID]) as [User],
         first.AllocUnitName
from sys.fn_dblog(NULL, NULL)  as first join
     sys.fn_dblog(NULL, NULL)  as second
on (first.[Transaction ID] = second.[Transaction ID])
where first.OPERATION = 'LOP_DELETE_ROWS'
and second.OPERATION = 'LOP_BEGIN_XACT'
order by first.AllocUnitName

What do you think?

 

Thank you in advance,

   Amos


Monday, April 20, 2015 - 1:35:04 PM - Sowmya Back To Top

Awesome Article ! This saved my time much more.. Thank you so much Manu :) 


Tuesday, December 02, 2014 - 12:54:39 PM - AZJim Back To Top

Manvendra ... thank you.  Very helpful.


Tuesday, October 21, 2014 - 3:22:49 AM - Jay Back To Top

Nice !!, is there a way to find the query text that caused the deletion?


Tuesday, October 14, 2014 - 10:22:06 PM - Zekamashi Back To Top

nice posting, well informed :) thanks


Tuesday, September 09, 2014 - 5:32:24 AM - Lydia Teleki Back To Top

Very informative psot,thanks a lot


Tuesday, July 08, 2014 - 10:30:10 AM - Manish Thouri Back To Top

Very nice post, very informative.


Thursday, July 03, 2014 - 2:55:32 AM - Sameer Mahat Back To Top

SELECT DatabaseID,ObjectName,NTUserName,name,ObjectID,HostName,LoginName,StartTime,*

FROM 

sys.fn_trace_gettable(CONVERT(VARCHAR(150), 

        ( SELECT TOP 1

                    f.[value]

            FROM    sys.fn_trace_getinfo(NULL) f

            WHERE   f.property = 2

        )), DEFAULT) T

JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

WHERE TE.trace_event_id IN(46,47) AND T.DatabaseName = 'DatabaseName'


Thursday, May 22, 2014 - 1:31:26 AM - ramki Back To Top

ITS HAVE A LOT OF DEPTH


Friday, May 09, 2014 - 9:09:39 AM - rick willemain Back To Top

What is the potential "harm" in using this function as described ?

 


Friday, May 02, 2014 - 11:15:41 PM - manu Back To Top

Nice post but, please do pay attention to what Runwin has posted above.


Monday, April 21, 2014 - 4:48:51 AM - Adinarayana Back To Top

Excellent Article.

 

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

can we recover that deleted data?

 

Thanks,

Adi

 


Friday, March 14, 2014 - 2:12:40 AM - rakesh Back To Top

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


Monday, March 03, 2014 - 6:12:47 AM - Ramesh M Back To Top

 

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


Monday, February 10, 2014 - 1:43:18 PM - Karthikeyan Jothi Back To Top

 

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, February 10, 2014 - 10:55:36 AM - Winston Daley Back To Top

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/

 


Thursday, January 30, 2014 - 7:29:08 AM - gaurav kaushik Back To Top

great man....


Thursday, January 30, 2014 - 5:48:07 AM - S Paruchuri Back To Top

Very useful article. Thanks.


Monday, January 20, 2014 - 10:00:50 AM - Fadhl AL-Sharee Back To Top

thankyou..


Thursday, January 02, 2014 - 5:52:48 PM - Jyo Back To Top

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


Wednesday, December 18, 2013 - 9:46:39 AM - Pramod Tiwari Back To Top

Thanx


Tuesday, December 17, 2013 - 1:30:11 PM - jamal Back To Top

nice


Tuesday, December 17, 2013 - 1:29:37 PM - jamal Back To Top

NICE ARTICLW


Sunday, December 15, 2013 - 9:38:16 PM - Mohamed Back To Top

Good


Thursday, December 12, 2013 - 11:08:13 AM - EHolland Back To Top

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.


Thursday, December 12, 2013 - 9:51:28 AM - Salem Back To Top

Awsome, tons of thanks


Saturday, December 07, 2013 - 11:49:50 PM - Gaurav Srivastava Back To Top

 

Thankyou 

Nice Article and very Informative

 

thanks once again

Gaurav Srivastava


Saturday, December 07, 2013 - 11:43:58 PM - Golam Back To Top

Very clear and well presented - thanks and pls keep posting

 


Tuesday, December 03, 2013 - 9:11:17 PM - Gopalakrishnan Back To Top

Excellent Article.......


Tuesday, December 03, 2013 - 12:28:40 PM - dyako Back To Top

Thanks,very much

 

 

 


Tuesday, December 03, 2013 - 9:11:47 AM - Deepak Back To Top

nice article


Tuesday, December 03, 2013 - 8:15:56 AM - Vikram Mahapatra Back To Top

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


Tuesday, December 03, 2013 - 3:53:22 AM - shravan Back To Top

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 - 12:35:32 AM - Wasim Back To Top

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.

 


Monday, December 02, 2013 - 10:12:53 PM - Chandu Back To Top

Very Informative, Thanx for sharing.


Monday, December 02, 2013 - 3:20:31 PM - PhyData DBA Back To Top

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


Monday, December 02, 2013 - 1:14:39 PM - John L Back To Top

Well done!


Monday, December 02, 2013 - 1:09:30 PM - John Back To Top

 

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 - 12:26:10 PM - Srinivas Back To Top

 

Hey,

 

Very nice article....and helpful


Monday, December 02, 2013 - 11:57:53 AM - RUnwin Back To Top

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 - 10:57:58 AM - Olu Back To Top

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 - 1:38:53 AM - Vaibhav Back To Top

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,

 


Learn more about SQL Server tools