SQL DROP TABLE Investigation Options

Problem

A database object has been dropped and you need to find out who did it and when they did it. For our examples we’re presuming there is no additional auditing in place.

Solution

There are a few methods to find out the who and the when for a database object that was dropped:

Method 1: Schema Changes History report built into SQL Server Management Studio (SSMS)
- Quickest and easiest method
- Dependent upon on Default Trace
Method 2: Reading the transaction log with fn_dblog function
- Still quick and easy
- It is an undocumented function
Method 3: Reading the transaction log with fn_dblog if the transaction log has been cleared
- Requires more effort
- Requires restoring backups to a test database incrementally

SQL Server Versions

Versions used for this tutorial:

Microsoft Windows Server 2019 Standard
SQL Server 2019 / CU8
SQL Server Management Studio (SSMS) 18.8

Setting Up

We’ll create a database called WhoDroppedTheObject, create a very simple table in it called TableToDrop without a primary key or foreign key constraint, then insert some random records into it. Here is the Transact-SQL syntax for the table definition and record insertion:

-- create database 
CREATE DATABASE [WhoDroppedTheObject];
GO
 
-- use WhoDroppedTheObject
USE [WhoDroppedTheObject]
GO
 
-- create table to drop
CREATE TABLE [dbo].[TableToDrop](
       [Col1] [nchar](10) NULL
);
GO
 
-- insert records
INSERT INTO [dbo].[TableToDrop]
VALUES ('abc'),
       ('def'),
       ('ghi');
GO
 
-- select from table
SELECT [Col1]
FROM [dbo].[TableToDrop];
GO

SQL DROP TABLE

Next step is to issue a DROP TABLE statement with the following syntax: DROP TABLE [dbo].[TableToDrop]. And of course, if we try to a SELECT * FROM [dbo].[TableToDrop] we’ll get an error saying it’s an invalid object because it’s a dropped table.

For the sake of this demonstration let’s pretend we aren’t the ones who just dropped the table in question and find out who the culprit is.

Method #1: Schema Changes History report built into SQL Server Management Studio (SSMS)

In the interest of efficiency, we’ll try the quickest and easiest way first which is the Schema Changes History report. This report is built into SQL Server Management Studio (SSMS) and is accessed from the Object Explorer. It gets its data from the from the default trace. It can be run on the server or individual database level. Here we’re interested in DDL changes in a particular database.

To access the report:

Expand Database dropdown
Right click on the database of interest
Reports
Standard Reports
Schema Changes History

Expand Object Name dropdown.

And we see the name of the dropped object, the time and date it was dropped, and who dropped it in one neat little report.

The caveat with this method is its dependency on the SQL Server Default Trace. Each Default Trace file rolls over when it reaches 20 MB and only 5 of the files are retained. Depending on the server’s activity, the DDL change data may or may not still be there when you run the report.

Method #2: Reading the transaction log with fn_dblog function

Fn_dblog is an undocumented SQL Server function that can read from a transaction log. There is always a risk with an undocumented feature and there is no guaranty that it will still exist in later versions of SQL Server but nevertheless it’s a useful tool.

Simply query the function as shown here.

 -- query tran log for 'DROPOBJ' with fn_dblog
SELECT [Begin Time]
      ,[Transaction Name]
      ,SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

The caveat with this method is it will not show us any data if the transaction log has been cleared. This will happen the first time the system runs a checkpoint in the database after the transaction log has been backed up, or if the database is in simple recovery model and the checkpoint automatically clears the log every minute or so.

In this example a transaction log backup is run, and a checkpoint is run manually to simulate a system run checkpoint.

-- clear tran log by backing up
BACKUP LOG [WhoDroppedTheObject] 
TO  DISK = N'D:\Backups\WhoDroppedTheObject_tran1.trn' 
WITH NOFORMAT, 
INIT,  
COMPRESSION,  
STATS = 10
GO
 
-- manually checkpoint to clear tran log
CHECKPOINT

Running the same query no longer returns data.

-- query tran log for 'DROPOBJ' with fn_dblog
SELECT [Begin Time]
      ,[Transaction Name]
      ,SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

Transaction is No Longer in Transaction Log

Method #3: Reading the transaction log with fn_dblog if the transaction log has been cleared

Here I’ve recreated the table, populated it, taken a full backup, taken 2 transaction log backups, dropped the new table, and taken 1 more transaction log backup for the current database. Here are the SQL statements:

-- use database
USE [WhoDroppedTheObject];
GO
 
-- create table to drop
CREATE TABLE [dbo].[TableToDrop](
       [Col1] [nchar](10) NULL
);
GO
 
-- insert records
INSERT INTO [dbo].[TableToDrop]
VALUES ('abc'),
       ('def'),
       ('ghi');
GO
 
-- select from table
SELECT [Col1]
FROM [dbo].[TableToDrop];
GO
 
-- full backup of WhoDroppedTheObject
BACKUP DATABASE [WhoDroppedTheObject] 
TO  DISK = N'D:\Backups\WhoDroppedTheObject_full.bak' 
WITH INIT, COMPRESSION, STATS = 10;
GO
 
-- tran log backup 1
BACKUP LOG [WhoDroppedTheObject] 
TO  DISK = N'D:\Backups\WhoDroppedTheObject_tran1.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO
 
-- tran log backup 2
BACKUP LOG [WhoDroppedTheObject] 
TO  DISK = N'D:\Backups\WhoDroppedTheObject_tran2.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO
 
-- drop table
DROP TABLE [dbo].[TableToDrop];
GO
 
-- tran log backup 3
BACKUP LOG [WhoDroppedTheObject] 
TO  DISK = N'D:\Backups\WhoDroppedTheObject_tran3.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO

Next, I created a new database called WhoDroppedTheObject_2, restored the full backup to it, and queried the transaction log with fn_dblog.

-- create new database to restore WhoDroppedTheObject backups to
CREATE DATABASE [WhoDroppedTheObject_2];
GO
-- restore full backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master] 
RESTORE DATABASE [WhoDroppedTheObject_2] 
FROM DISK = N'D:\Backups\WhoDroppedTheObject_full.bak' 
WITH FILE = 1, 
     MOVE N'WhoDroppedTheObject' 
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2.mdf', 
     MOVE N'WhoDroppedTheObject_log' 
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2_log.ldf', 
     NOUNLOAD, 
     RECOVERY, 
     REPLACE, 
     STATS = 5; 
GO 
 
-- query tran log for 'DROPOBJ' with fn_dblog 
SELECT [Begin Time] 
      ,[Transaction Name] 
      ,SUSER_SNAME ([Transaction SID]) AS [User] 
FROM fn_dblog (NULL, NULL) 
WHERE [Transaction Name] = N'DROPOBJ'; 
GO

Fn_dblog returned no records so next step is to restore the full backup followed by the first and second transaction log backups.

-- restore full backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master]
RESTORE DATABASE [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_full.bak'
WITH FILE = 1,
     MOVE N'WhoDroppedTheObject'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2.mdf',
     MOVE N'WhoDroppedTheObject_log'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2_log.ldf',
     NOUNLOAD,
     NORECOVERY,
     REPLACE,
     STATS = 5;
GO
 
-- restore 1st tran log backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master];
GO
RESTORE LOG [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_tran1.trn'
WITH NOUNLOAD,
STATS = 5;
GO
 
-- query tran log for 'DROPOBJ' with fn_dblog
USE [WhoDroppedTheObject_2];
GO
SELECT [Begin Time]
      ,[Transaction Name]
      ,SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

Still, no records. Restore the full backup followed by the first, second, and third transaction log backups.

-- restore full backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master]
RESTORE DATABASE [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_full.bak'
WITH FILE = 1,
     MOVE N'WhoDroppedTheObject'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2.mdf',
     MOVE N'WhoDroppedTheObject_log'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WhoDroppedTheObject_2_log.ldf',
     NOUNLOAD,
     NORECOVERY,
     REPLACE,
     STATS = 5;
GO
 
-- restore 1st tran log backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master];
GO
RESTORE LOG [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_tran1.trn'
WITH NOUNLOAD,
NORECOVERY,
STATS = 5;
GO
 
-- restore 2nd tran log backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master];
GO
RESTORE LOG [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_tran2.trn'
WITH NOUNLOAD,
NORECOVERY,
STATS = 5;
GO
 
-- restore 3nd tran log backup of WhoDroppedTheObject to WhoDroppedTheObject_2 
USE [master];
GO
RESTORE LOG [WhoDroppedTheObject_2]
FROM DISK = N'D:\Backups\WhoDroppedTheObject_tran3.trn'
WITH NOUNLOAD,
STATS = 5;
GO
 
-- query tran log for 'DROPOBJ' with fn_dblog
USE [WhoDroppedTheObject_2];
GO
SELECT [Begin Time]
      ,[Transaction Name]
      ,SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

The table was dropped between the second and third transaction log backups, so we are able to find the culprit.

This method is clearly more time consuming than the first two and would become even more so if the database was very large and transaction logs were backed up frequently but would still solve the mystery for us.

Next Steps

Now that we have some additional tools to find out who dropped an object in our toolbox here are some links with more information:

Resources

Community

Subscribe