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
- We will create some sample table with data
- Delete rows from it
- 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)
(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%)
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
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'
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'
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'
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
[Begin Time], [End Time]
[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
**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 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
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