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.

SQL SERVER - Finding User Who Dropped Database Table userdropped

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.

SQL SERVER - Finding User Who Dropped Database Table schema1

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.

SQL SERVER - Finding User Who Dropped Database Table schema2

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)

, , , ,
Previous Post
SQL SERVER – Results of Dynamic SQL Into A Variable
Next Post
SQL SERVER – Huge Size of SSISDB – Catalog Database SSISDB Cleanup Script

Related Posts

11 Comments. Leave new

  • Helpful

    Reply
  • Works nice for direct access to the SQL database however a lot of db operations can be masked by a service tier… it’s a pain in my world.

    Reply
  • Nice

    Reply
  • I assume this information is stored somewhere. Where is it stored? In a DMV? If so, which one?

    Reply
  • How can I query this information using T-SQL?

    Reply
  • You can directly go to Schema Changes History by
    First right click on the database – Go to Reports >> Schema Changes History.

    Reply
    • Hi developer, it seems like you are repeating the content of the post. Please take the time to understand my question. As an analogy, if I ask about a query to create a table and you tell me to right click on the tables folder and select create table, those are two different things. I want to use a query to access the same information as described in the post. Maybe it’s not possible. If it is, I feel curious and want to know how.

      Reply
      • you can get the query with the help of profiler. just run the profiler and do the actions in the sql management, profiler will log the queries for you!

  • Hi Pinal,

    How about if database itself deleted accidentally and we would like to know who does this ?

    Reply
  • You can Query the Default trace File using T-SQL to get the above information.

    Here is the Query to find the default trace file in SQL Server

    SELECT path AS [Default Trace File] ,max_size AS [Max File Size of Trace File] ,
    max_files AS [Max No of Trace Files] ,start_time AS [Start Time] ,
    last_event_time AS [Last Event Time] FROM sys.traces WHERE is_default = 1
    GO

    Then you can find out who Created and deleted Databases from SQL Server

    Get the default trace information in a Temporary table and then query the temporary table to get the required information

    USE tempdb
    GO
    IF OBJECT_ID (‘dbo.TraceTable’, ‘U’) IS NOT NULL
    DROP TABLE dbo.TraceTable;

    SELECT * INTO TraceTable
    FROM ::fn_trace_gettable
    (‘Default trace file Path’, default)
    GO

    SELECT DatabaseID ,DatabaseName ,LoginName ,HostName ,ApplicationName ,StartTime ,
    CASE
    WHEN EventClass = 46 THEN ‘Database Created’
    WHEN EventClass = 47 THEN ‘Database Dropped’
    ELSE ‘NONE’
    END AS EventType
    FROM tempdb.dbo.TraceTable
    WHERE DatabaseName = ‘dbname’
    AND (EventClass = 46 /* Event Class 46 refers to Object:Created */ OR EventClass = 47)
    /* Event Class 47 refers to Object:Deleted */
    GO

    Change the Eventclass parameter to get another information.

    Reply

Leave a Reply

Menu