Who Delete My Data (SQL)

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

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:

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

select * from fn_dblog(null,null)

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

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

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

Result:

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

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

Result:

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.

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

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

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'

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

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

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

Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'

Check the below query

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

I got below result

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