How to find out who deleted some data SQL Server

Question

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).

I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?

Answer 1

I've not tried fn_dblog on Express but if it is available the following will give you delete operations:

SELECT 
    * 
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

Answer 2

If database is in full recovery mode or if you have transaction log backups you can try to read these using third party log readers.

You can try ApexSQL Log (premium but has a free trial) or SQL Log Rescue (free but sql 2000 only).

Answer 3

how they could find out who deleted some data in their SQL Server database

Although this is answered, wanted to add that SQL Server has a default trace enabled and it can be used to find out who dropped/altered the objects.

Object events

Object events include: Object Altered, Object Created and Object Deleted

note: SQL Server by default has 5 trace files, 20 MB each and there is no known supported method of changing this. If you have a busy system, the trace files may roll over far too fast (even within hours) and you may not be able to catch some of the changes.

Excellent example can be found : The default trace in SQL Server - the power of performance and security auditing

Answer 4

You could try this procedure to query the log backup files and find in which log backup file(s) a specific value of a column of a table was still/last present.

To find the user, after you find in what log backup the value last existed, you can restore a database until that log backup and then follow Mark Storey-Smith's answer.

Some prerequisites

• know what values from which columns were deleted
• Are under the full recovery model and are taking log backups
• you have dates or identifiers in your log backups, such as when using Ola Hallengren's solution

Disclaimer

This solution is far from waterproof, and much more work needs to go into it.

It has not been tested on large scale environments, or even any environments apart from a few small tests. Current run was on SQL Server 2017.

You could use below procedure from Muhammad Imran that I modified to work with the contents of log backups instead of the contents of a live database's log.

This way you are technically not doing restores, but instead dumping the log contents in a temporary table. It will probably still be slow, and is very open to bugs and issues. But it could work, in theory™.

The stored procedure uses the undocumented fn_dump_dblog function to read out the log files.

---

Testing environment

Consider this database, where we insert some rows, take 2 log backups, and on the third log backup we delete all rows.

CREATE DATABASE WrongDeletesDatabase
GO
USE WrongDeletesDatabase
GO
BACKUP DATABASE WrongDeletesDatabase TO DISK ='c:\temp\Full.bak'

ALTER DATABASE WrongDeletesDatabase SET RECOVERY FULL
GO

CREATE TABLE dbo.WrongDeletes(ID INT, val varchar(255))

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2.trn'
GO
DELETE FROM dbo.WrongDeletes
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4.trn'
GO

The procedure

You can find and download the stored procedure here.

I could not add it here since it is bigger than the character limit, and would make this answer even less clear than it is.

Apart from this, you should be able to run the procedure.

Running the procedure

An example of this, when I add all my log files (4) to the stored procedure & run the procedure looking for value1

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

This gets me:

ID  val LogFileName
1   value1  c:\temp\Logs\log3.trn
1   value1  c:\temp\Logs\log1.trn

Where we can find when the last time an operation on value1 happened, the delete in log3.trn.

Some more test data, adding a table with different columns

CREATE TABLE dbo.WrongDeletes2(Wow varchar(255), Anotherval varchar(255),Val3 int)

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('b','value1',1)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('c','value2',2)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2_1.trn'
GO
DELETE FROM dbo.WrongDeletes
DELETE FROM dbo.WrongDeletes2
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('d','value3',3)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4_1.trn'
GO

Changing the log file names & executing the procedure again

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

Result

ID  val LogFileName
1   value1  c:\temp\Logs\log1_1.trn
1   value1  c:\temp\Logs\log3_1.trn
1   value1  c:\temp\Logs\log3_1.trn

A new run, searching for the integer (2) in the val3 column of dbo.WrongDeletes2

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes2', 
                                    @SearchString = '2', 
                                    @SearchColumn = 'Val3',
                                    @LogBackupFolder ='C:\temp\Logs\'

Result

Anotherval  Val3    Wow LogFileName
value2  2   c   c:\temp\Logs\log2.trn
value2  2   c   c:\temp\Logs\log3.trn

Applying Mark Storey-Smith's answer

We know now that it happened in the third log file, let's restore until that point:

USE master
GO
ALTER DATABASE WrongDeletesDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE WrongDeletesDatabase SET ONLINE 
GO
RESTORE DATABASE WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\Full.bak' WITH NORECOVERY,REPLACE
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log1.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log2.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log3.trn' WITH RECOVERY
GO
USE WrongDeletesDatabase
GO

Running the last query in his answer

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

Result for me (sysadmin)

UserName    TransactionStartTime
dbo 2019/08/09 17:14:10:450
dbo 2019/08/09 17:14:10:450