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

By:   |   Comments (54)   |   Related: 1 | 2 | 3 | 4 | More > Database Administration


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

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];
-- Create tables.
USE ReadingDBLog;
CREATE TABLE [Location] (
    [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 100

Step 3

Now go ahead and delete some rows to check who has deleted your data.

USE ReadingDBLog
DELETE Location WHERE [Sr.No]=10
SELECT * FROM Location WHERE [Sr.No]=10
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
    [Transaction ID],
    fn_dblog(NULL, NULL) 
    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
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
    fn_dblog(NULL, NULL)
    [Transaction ID] = '0000:000004ce'
    [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.

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

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
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

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 Steps

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, October 10, 2019 - 6:29:50 AM - tsqlNovice Back To Top (82726)


thanks for this great article, just one question is it possible to know who execute an update ?

Because our problem , we'd like to find who has updated SOME rows in database in enviroment of PRODUCTION !

Of course, if we ask the question to my colleague it's none of them :) :)

thanks for all

Friday, October 12, 2018 - 4:25:06 AM - Irfan Mansuri Back To Top (77929)

thank you for the very helpful tip.

But is it possible to get Table which has been dropped?


Tuesday, July 24, 2018 - 11:51:27 AM - Greg Robidoux Back To Top (76810)

Hi Muhammad,

you need to run the command like this:



Tuesday, July 24, 2018 - 11:47:49 AM - Muhammad Farhan Back To Top (76809)

Hi, When I excute query to obtain user name i.e. SELECT * FROM SUSER_SNAME(0x01)  , it displays error "invalid object name 'SUSER_SNAME', even SUSER_SNAME shows in pink color... what should i do?


Friday, March 30, 2018 - 10:16:28 PM - SK Raju Back To Top (75566)

 Nice, I have not tried, but looks good. Thank you so much.

Saturday, November 11, 2017 - 8:29:08 PM - [email protected] Back To Top (69598)

 Does the recovery model matters for reading that kind of log for drop column etc?


Saturday, July 8, 2017 - 12:42:08 PM - Charaka Dharma Back To Top (59091)

Still learning new stuff every day!

Thursday, June 1, 2017 - 11:41:18 AM - Megha Back To Top (56423)

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 (46768)

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


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

 HI Manvendra ,

very useful article. Thanks.

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

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




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 (41302)

On point. Thanks for sharing.

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

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],
         second.[Begin Time] as [Deletion time],
         second.[Transaction Name],
         SUSER_SNAME(second.[Transaction SID]) as [User],
from sys.fn_dblog(NULL, NULL)  as first join
     sys.fn_dblog(NULL, NULL)  as second
on (first.[Transaction ID] = second.[Transaction ID])
order by first.AllocUnitName

What do you think?


Thank you in advance,


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

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

Tuesday, December 2, 2014 - 12:54:39 PM - AZJim Back To Top (35465)

Manvendra ... thank you.  Very helpful.

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

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 (34961)

nice posting, well informed :) thanks

Tuesday, September 9, 2014 - 5:32:24 AM - Lydia Teleki Back To Top (34449)

Very informative psot,thanks a lot

Tuesday, July 8, 2014 - 10:30:10 AM - Manish Thouri Back To Top (32591)

Very nice post, very informative.

Thursday, July 3, 2014 - 2:55:32 AM - Sameer Mahat Back To Top (32527)

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



        ( SELECT TOP 1


            FROM    sys.fn_trace_getinfo(NULL) f

            WHERE = 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 (30881)


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

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


Friday, May 2, 2014 - 11:15:41 PM - manu Back To Top (30598)

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 (30208)

Excellent Article.


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

can we recover that deleted data?





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

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

Monday, March 3, 2014 - 6:12:47 AM - Ramesh M Back To Top (29625)


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 (29396)


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 (29388)

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.


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

great man....

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

Very useful article. Thanks.

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


Thursday, January 2, 2014 - 5:52:48 PM - Jyo Back To Top (27942)

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 (27829)


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


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


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


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


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:

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

Thanks again.

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

Awsome, tons of thanks

Saturday, December 7, 2013 - 11:49:50 PM - Gaurav Srivastava Back To Top (27732)



Nice Article and very Informative


thanks once again

Gaurav Srivastava

Saturday, December 7, 2013 - 11:43:58 PM - Golam Back To Top (27731)

Very clear and well presented - thanks and pls keep posting


Tuesday, December 3, 2013 - 9:11:17 PM - Gopalakrishnan Back To Top (27679)

Excellent Article.......

Tuesday, December 3, 2013 - 12:28:40 PM - dyako Back To Top (27674)

Thanks,very much




Tuesday, December 3, 2013 - 9:11:47 AM - Deepak Back To Top (27669)

nice article

Tuesday, December 3, 2013 - 8:15:56 AM - Vikram Mahapatra Back To Top (27668)

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

Tuesday, December 3, 2013 - 3:53:22 AM - shravan Back To Top (27666)

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 3, 2013 - 12:35:32 AM - Wasim Back To Top (27662)

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 2, 2013 - 10:12:53 PM - Chandu Back To Top (27661)

Very Informative, Thanx for sharing.

Monday, December 2, 2013 - 3:20:31 PM - PhyData DBA Back To Top (27656)

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

Monday, December 2, 2013 - 1:14:39 PM - John L Back To Top (27653)

Well done!

Monday, December 2, 2013 - 1:09:30 PM - John Back To Top (27652)




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 2, 2013 - 12:26:10 PM - Srinivas Back To Top (27651)




Very nice article....and helpful

Monday, December 2, 2013 - 11:57:53 AM - RUnwin Back To Top (27650)

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 2, 2013 - 10:57:58 AM - Olu Back To Top (27649)

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 2, 2013 - 1:38:53 AM - Vaibhav Back To Top (27646)

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.




get free sql tips
agree to terms