How to Check Who Dropped the Table in SQL Server Database?

In this blog, we will learn how to check who dropped the table in SQL Server using the top 2 solutions. By reading this entire post, SQL Server users can easily get the perfect solution. However, users must be attentive throughout the blog to identify the user who dropped a table in SQL Server without facing any difficulties.

In the coming section, we will discuss the three manual solutions on how to find out who dropped a table in SQL Server.

In this blog, we will learn how to check who dropped the table in SQL Server using the top 2 solutions. By reading this entire post, SQL Server users can easily get the perfect solution. However, users need to be attentive throughout the blog to identify the user who dropped a table in SQL Server without facing any difficulties.

In the coming section, we will discuss the three manual solutions on how to find out who dropped a table in SQL Server.

How to Check Who Dropped the Table in SQL Server Database Manually?

The solutions to check who drops table in SQL Server with the below steps:

Solution 1: Schema Changes History Report Developed Into SQL Server Management Studio.

To quickly and efficiently identify SQL database schema changes, we’ll utilize the Schema Changes History Report within SQL Server Management Studio (SSMS) and this can be accessible through Object Explorer. It uses default traces for data and can be run for a particular database or entire SQL Server. Here we’ll focus on DDL command changes in a specific SQL database.

To view the Schema Changes History Report 

• Extend the SQL Database menu.
• Now, right-click on the SQL Server database the user is interested in.
• Navigate to Reports >> Standard Reports >> Schema Changes History.
• Expand the Object Name dropdown tab.

 

Last SQL user can view the name of dropped tables, the time and data if it dropped, and who dropped it in one single report.

The problem with this method is that it depends on the SQL Server’s default trace.

Solution 2: Review the SQL transaction log using the fn_dblog function. 

Fn_dblog () function is one of many undocumented functions in the MS SQL Server database. It allows the user to view the SQL transaction log records in the most active part transaction log file for the existing SQL database.

An example of the function fn_dblog is shown here.

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

The function with this solution will not show data in the SQL transaction log has been cleared. 

-- clear Tran log by backing up
BACKUP LOG [UserDroppedThetable] 
TO  DISK = N'D:\Backups\UserDroppedThetable_tran1.trn' 
WITH NO FORMAT, INIT,  COMPRESSION,  STATS = 10
GO
— manually checkpoint to clear tran log
CHECKPOINT


Execute the same command no longer returns data.

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

Solution 3: Analyzing the SQL transaction log using fn_dblog after it has been cleared 

In the method, the user recreated the table, used it took a full backup of it took 2 transaction log backups dropped the new table and took one more transaction log backup for the existing SQL database.

— Switch to the target database

USE [UserDroppedThetable];
GO

— Create a new table named TableuserDrop

CREATE TABLE [dbo].[TableuserDrop](
      NULL
);
GO

— Insert data into the TableuserDrop table

INSERT INTO [dbo].[TableuserDrop]
VALUES ('back),
       ('def'),
       ('ghi');
GO

— Retrieve data from TableuserDrop

SELECT [Col1]
FROM [dbo].[TableuserDrop];
GO

— Perform a full backup of the UserDroppedThetable database

BACKUP DATABASE [UserDroppedThetable] 
TO DISK = N'D:\Backups\UserDroppedThetable_full.bak' 
WITH INIT, COMPRESSION, STATS = 10;
GO

— Now Perform the first transaction log backup

BACKUP LOG [UserDroppedThetable] 
TO DISK = N'D:\Backups\UserDroppedThetable_tran1.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO

— Perform the second transaction log backup

BACKUP LOG [UserDroppedThetable] 
TO DISK = N'D:\Backups\UserDroppedThetable_tran2.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO

— Remove the TableToDrop table

DROP TABLE [dbo].[TableToDrop];
GO

— Perform the third transaction log backup

BACKUP LOG [UserDroppedThetable]
TO DISK = N'D:\Backups\UserDroppedThetable_tran3.trn' 
WITH INIT, COMPRESSION, STATS = 10;
GO

Now, a user created a new SQL database called WhoDroppedTheObject_2, restored the complete backup of it, and used the transaction log fn_dblog

— Create a new database to restore UserDroppedThetable backups to

CREATE DATABASE [UserDroppedThetable_2];
GO

-- Restore full backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master] 
RESTORE DATABASE [UserDroppedThetable_2] 
FROM DISK = N'D:\Backups\UserDroppedThetable_full.bak' 
WITH FILE = 1, MOVE N'UserDroppedThetable' 
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\UserDroppedThetable_2.mdf', 
     MOVE N'UserDroppedThetable_log' 
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\UserDroppedThetable_2_log.pdf, 
     DOWNLOAD, 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

Function fn_dblog returned no SQL records, and so in next step is to restore the full backup followed by the first and second log backups. 

— Restore full backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master]
RESTORE DATABASE [UserDroppedThetable_2]
FROM DISK = N'D:\Backups\UserDroppedThetable_full.bak'WITH FILE = 1,
     MOVE N'UserDroppedThetable TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\UserDroppedThetable2.mdf',
     MOVE N'UserDroppedThetable_log'TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATAUserDroppedThetable_2_log.pdf,
     DOWNLOAD,NO RECOVERY,REPLACE,STATS = 5;
GO 

— restore 1st tran log backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master];
GO
RESTORE LOG [UserDroppedThetable2]
FROM DISK = N'D:\Backups\UserDroppedThetable_tran1.trn'
WITH NOUNLOAD,STATS = 5;
GO

— query Tran log for ‘DROPOBJ’ with fn_dblog

USE [UserDroppedThetable_2];
GO
SELECT [Begin Time],[Transaction Name],SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

Users need to ensure there are no missing records by restoring the full backup, and then first, second, and third SQL transaction log backups.

— Restore full backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master]
RESTORE DATABASE [UserDroppedThetable_2]
FROM DISK = N'D:\Backups\UserDroppedThetable_full.bak'
WITH FILE = 1,
     MOVE N'UserDroppedThetablet'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\UserDroppedThetable2.mdf',
     MOVE N’UserDroppedThetable_log'
     TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\UserDroppedThetable_2_log.pdf,
     DOWNLOAD, NO RECOVERY, REPLACE, STATS = 5;
GO
-- restore 1st tran log backup of UserDroppedThetable to UserDroppedThetable2 

USE [master];
GO
RESTORE LOG [v_2]
FROM DISK = N'D:\Backups\UserDroppedThetable_tran1.trn'
WITH NOUNLOAD,NO RECOVERY,STATS = 5;
GO

-- restore 2nd tran log backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master];
GO
RESTORE LOG [UserDroppedThetable_2]
FROM DISK = N'D:\Backups\UserDroppedThetable_tran2.trn'
WITH NOUNLOAD,NO RECOVERY,STATS = 5;
GO

 — Restore 3rd tran log backup of UserDroppedThetable to UserDroppedThetable_2 

USE [master];
GO
RESTORE LOG [UserDroppedThetable_2]
FROM DISK = N'D:\Backups\UserDroppedThetable_tran3.trn'
WITH NOUNLOAD,STATS = 5;
GO

— query Tran log for ‘DROPOBJ’ with fn_dblog

USE [UserDroppedThetable];
GO
SELECT [Begin Time],[Transaction Name],SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';
GO

The SQL table drooped between the second and third SQL transaction backups, so users can identify the person who dropped the SQL records.

Limitations of Manual Method 

• All the manual methods are time-consuming or ineffective, and it does not give quick responses to the user.
• Many users lack proficiency in running MS SQL Server commands due to a lack of technical knowledge.
• In the manual method, each of the steps is important as it is directly related to the SQL Server database.
• Loss of information is very high in manual methods, as it sometimes corrupts SQL Server files.

Also Read: SQL Server Error 9002 Solution Explained?

How to Check Who Dropped the Table in SQL Server Database Using Smart Solution?

While searching how to find out who dropped a table in SQL Server manually, users might fail. Then it can use a reliable SQL Log Analyzer Tool to quickly identify the person who dropped the table in SQL Server. The software completely analyzes the SQL transaction log (.ldf) file to detect the important changes in the SQL Server records. The tool to Open, Analyze and Read all MS SQL Server transactions such as INSERT, DELETE, and UPDATE. It allows SQL users to connect with the Online MS SQL Server Database by utilizing login identifications to automatically fetch SQL records. 

Download Now Purchase Now

The software supports MS SQL Server versions such as 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, and 2005 LDF files. It is used for forensic analysis to keep track of who changes the SQL table records elaborately. The application provides 3 different export options such as CSV file, SQL Server database, and SWL Server Compatible Scripts. It keeps track of all transactional activity in the MS SQL Server database. Besides that the software supports all Windows Operating Systems like (11, 10, 8.1, 8, 7, and XP) with both 64-bit and 32-bit versions. The program works in both Online and Offline mode. 

Follow these Steps to How Check Who DROP Table in SQL Server

• Start downloading and launching the software program on your computer.
• Now, click open and add the .ldf/.mdf file to the software.
• Here, the user needs to select the option to add a file like Online DB Option and Offline DB Option.
• Now, users can view the entire scanning information such as No. of Inserts, No. of Deletes, etc. 
• Select the Transaction field’s name section to arrange the data in ascending or descending order.
• Choose the transaction record type the user wishes to export, like INSERT, DELETE and UPDATE.
• Next, select the export option to save the data such as CSV, SQL Server Database and SQL Server Compatible Script.
• Lastly, click on the Export option to start the process and save the file on your computer.

Final Thought 

In this blog, we have discussed the two most effective methods for finding who dropped records from a table in SQL Server. It’s recommended to opt for manual recovery steps if you possess administrator-level knowledge of SQL Server. It is strongly recommended for non-technical users to use a recommended smart solution to resolve their issues quickly.