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]
Fig 1. Results

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,

Fig 2. Invalid object Name

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
Fig 3. List of Users who has dropped the tables

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,

Fig 4. Select Database in SSMS

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.

Fig 5. Schema Change History
Fig 6. List of Changes in Database

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

WordPress.com.

Up ↑

%d bloggers like this: