Find user who ran DROP statements – SQL Server

Introduction

Sometimes we need to track or find which user has DROPPED the table(s) from the Database. In this article, we will discuss the possible ways to track this situation.

I have a table already in my Database,

SELECT 
     [StudentId],
     [FirstName],
     [LastName]
FROM [StudentManagementSystem].[Students].[StudentDetails]

Drop table

Now I am going to apply the Drop Statement to drop the table.

DROP TABLE [StudentManagementSystem].[Students].[StudentDetails]

Now the table is dropped, and if we run the SELECT statement for the above table, we will get an error as,

Option 1

The first option is by using the fn_dblog() function. fn_dblog() is undocumented functions in SQL Server. This function give the transaction logs records in the active part of the transaction log file for the current database.

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

When we run the above statements in the respective Database, it will list the results of users and the tables dropped. The result column Transaction SID holds the user name.

Option 2

The second option is without running the SQL Statements.

Step 1: Go to the SSMS and select the Database as shown below,

In my case, StudentManagementSystem is the database where I have deleted the table.

Step 2: Right click the database, go to Reports -> Standard Reports -> Schema Changes History.

Once as followed the Step 2, we could able to see a list of changes done in that database as shown in the Figure 6.

Conclusion

By using the SQL transaction statement and from Reports options in SSMS we can easily find the user who ran DROP Statement. I hope you all found this much useful. Please share you feedback in the comment section.

Disclaimer – The views/contents shared/authored in this article are mine and not of my employer.