Click here to Skip to main content
15,867,594 members
Articles / ADO.NET

Who Delete My Data (SQL)

Rate me:
Please Sign up or sign in to vote.
4.70/5 (17 votes)
26 Dec 2017CPOL4 min read 14K   21   7
Have you lost your data from SQL table? Have you lost your SQL table? Want to know who ran DELETE or DROP Query on your database? Then go through this step by step article, it will help you to find the culprit

Image 1

Introduction

Last month my friend has called me and said, some has delete his important data from SQL table and now no one confess it. He asked me to search a way to find culprit. After couple of here and there i got a way and now want to share with you. So, let's enjoy this article.

This article will help you find the user who fire DELETE or DROP statement on your table or database.

Things we need

To search culprit, we need to read transaction log entries of database. Yes...You heard is right, you can read SQL transaction log data (i.e. LDF file).  let's begin with the steps

  1. We will create some sample table with data
  2. Delete rows from it
  3. Try to track the user who delete (soft or hard) data entries (Here Soft Delete means delete records using Query and Hard delete indicates delete data using 'DEL' button (or may be with mouse) from SQL table directly)
Let's start

LDF :

(Those who don't know what is LDF) LDF is a file extension for log data files these files are exist with MDF files (which contains actual data). LDF file store all transactions with time stamp and help to recover database in case of data loss.

Now, to read LDF file we need to use 'fn_dblog' function, (which is undocumented function of SQL), after executing this function on particular database you will able to see live transaction logs and operations executed on that database.

Let's create sample database and table, with the help of following Query

SQL
CREATE DATABASE [Sample] ON  PRIMARY
( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
 LOG ON
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
GO

sample table:

SQL
USE [Sample]
GO
CREATE TABLE [Emp] (
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)
);

Now let's check what has been recorded in LDF logs

run 'fn_dblog' function in Sample database

SQL
select * from fn_dblog(null,null)

Image 2

If you see above result pane, there are almost 35 rows are recorded for just CREATE DATABASE and CREATE TABLE script

Let's insert some rows in table

SQL
Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

Track DELETE Activity

Now, just go and delete all rows from database, use below simple query

Delete from Emp

Our Emp table is now empty as, we have delete all the queries

Let's examine the log table, having operation type is 'LOP_DELETE_ROWS', fire fn_dblob function again and see what you get

SQL
select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'

Result:

Image 3

Above result pane show us, all the transaction rows which are having 'DELETE' entries on specific database table, you need to search for the your 'specific' table (from where you have lost your data), check out column 'AllocUnitName', this column contains your table name on which 'DELETE' statement has fired.

In our case, Table name is 'Emp', now get the transaction ID for that particular 'table' entry record, execute below query to get record of particular table

SQL
select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'

Result:

Image 4

In our case, transaction ID is same, as all entries are deleted with single 'DELETE' statement (at once) (e.g.  0000:0000079f)

With the help of above transaction ID, we will find when our entries are deleted from database. For that purpose we need to search record with operation LOP_BEGIN_XACT, fire below query on database.

SQL
select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

The above query will give you Start time of the transaction.

Now we got the exact time when someone fire DELETE query on database, to know the activity End Time, you can try below query

SQL
SELECT
   [Begin Time], [End Time]
FROM
    fn_dblog(NULL, NULL)
WHERE
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

Here is the result of above query

Image 5

Now let's find who is the culprit, we will find the real database user who fire delete query

Transaction SID column contains encrypted Hexa decimal text which is nothing but the user name who fire 'Delete' query

Fire below query to get [Transaction SID] column with the help of Transaction ID and Operation = 'DELETE'

SQL
select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

Output of above query is

Image 6

Just you need to copy, encrypted hexadecimal contents from [Transaction SID] column and execute below query on master database, as per our result my Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

**SUSER_SNAME is the inbuilt function, it just checks security identification number (SID) and back with the login name associated it.

When I run above query I got below output

Image 7

Yes...we finally got real culprit who fire Delete query

Track DROP activity

Similarly, if anyone DROP your table from database we can track that activity by using following queries,

Let's Drop table with below simple query

SQL
Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'

Check the below query

SQL
SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

I got below result

Image 8

Same as DELETE scenario execute below query on master database, as per our result Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

which is the same user 'sa'

So, to Conclude

SQL store all its transactions in log table, we can read transaction log file using fn_dblob function, we can do more research on each transaction with the help of this function.  All transactions are logged with different operations,  With the help of SUSER_SNAME function we can easily trace out encrypted user name.

In my next article I will cover deep dive points on 'Reading Transaction Log of SQL (LDF)', so Please stay tuned

and Enjoy this article

**DO NOT alter enties of fn_dblog or DO NOT run these command on production unless you have backup. 

*Suggestions and comments are always welcome 

-Happy Tracing

Prasad

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Hi there, I am Prasad. Author, Blogger, contributor and passionate about Microsoft .NET technologies. I like to write an articles/blogs on different .NET aspects and like to help Developers, to resolve their issues and boost them on Microsoft Technologies.


Certifications: Microsoft Certified professional (MCP), Microsoft Certified technology specialist (MCTS), Agile-Scrum Master.


Awards: Microsoft Re-connect MVP (GSC Member), Most valuable member at dotnetspider, Most popular curator, Most active curator, featured curator at Microsoft Curah, Editor at dotnetspider.


Microsoft MVP 2014 [ASP.NET/IIS]
Click here for more .NET Tips
-After all Knowledge is an endless entity

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mathi Mani4-Jan-18 8:42
Mathi Mani4-Jan-18 8:42 
GeneralRe: My vote of 5 Pin
koolprasad20037-Mar-18 21:34
professionalkoolprasad20037-Mar-18 21:34 
QuestionPls clarify Pin
Mathi Mani3-Jan-18 6:57
Mathi Mani3-Jan-18 6:57 
AnswerRe: Pls clarify Pin
koolprasad20033-Jan-18 18:35
professionalkoolprasad20033-Jan-18 18:35 
GeneralRe: Pls clarify Pin
Mathi Mani4-Jan-18 8:44
Mathi Mani4-Jan-18 8:44 
QuestionWhich DBMS? Pin
mark.hansen6228-Dec-17 8:50
mark.hansen6228-Dec-17 8:50 
AnswerRe: Which DBMS? Pin
koolprasad200329-Dec-17 17:19
professionalkoolprasad200329-Dec-17 17:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.