SQL SERVER – Finding User Who Dropped Database Table

Though I mainly focus on SQL Server Performance Tuning as my primary consulting engagement, there are moments when I am asked questions from my regular clients which are not related to SQL Server Performance and they still expect me to answer. I think it is totally fair, no one wants to go to find a new consultant for some simple questions. Recently my customer wanted to know who dropped the database table from their primary database.

Let us see how I was able to help my customer to find the user who dropped their important table from their system.

Let us first recreate the scenario. I am using SQL Server 2017 but you can use an earlier version of SQL Server as well.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT)
GO
ALTER TABLE TestTable 
ADD FirstCol VARCHAR(100)
GO

Now we have just created a new database and create a table in it. We have also changed the table definition by adding an additional column.

Now let us go ahead and drop the table by running the following command.

DROP TABLE TestTable
GO

Now the challenge is how to find out who dropped the table which was created in the database. There is a simpler way to do the same.

First right click on the database – Go to Reports >> Standard Reports >> Schema Changes History.

Once you are in the Schema Changes History, you will see a report which is very similar to following. Over here, expand any object which you are interested in and you will see necessary details associated with the same.

For example, I am interested in TestTable and you can see it shows the three rows of schema change. You can also see the name of the user and time when he/she dropped the table.

I hope you find this trick useful. I strongly suggest that you bookmark this blog post as you will for sure find it useful when you want to know who has changed anything in your database schema quickly.

Reference: Pinal Dave (https://blog.sqlauthority.com)