Who Dropped the SQL Table


By:   |   Updated: 2021-04-29   |   Comments   |   Related: More > Monitoring


Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Free MSSQLTips Webinar: Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Attend this webinar to learn how to manage the performance of SQL Server not only on-premises but also in the cloud for Azure SQL Database, Amazon RDS for SQL Server as well as SQL Server using a single tool.


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
Demo Preparation

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.

Drop 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:

  1. Expand Database dropdown
  2. Right click on the database of interest
  3. Reports
  4. Standard Reports
  5. Schema Changes History
Run Schema Changes History Report

Expand Object Name dropdown.

Schema Changes History Report

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
Query Transaction Log with fn_dblog

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
Backup Transaction Log

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
find out who dropped sql server object

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
find out who dropped sql server object

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
find out who dropped sql server object

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.

find out who dropped sql server object

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:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2021-04-29

Comments For This Article





download














get free sql tips
agree to terms