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)
12 Comments. Leave new
Helpful
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.
Nice
I assume this information is stored somewhere. Where is it stored? In a DMV? If so, which one?
How can I query this information using T-SQL?
You can directly go to Schema Changes History by
First right click on the database – Go to Reports >> Schema Changes History.
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.
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 ?
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.
Hi @Pinal, Thanks for this article, its really helpful. But I want to know-
1. Is there DMV’s through which I can get this data ?
2. Is there any way to save the report data ?
3. Is there way to get which user get dropped on a database level ? I have a use case – in a database, we have some users connecting to the database and a day, one user getting permission issue during connection. What’s the cause for this and how can I get it ?
Thanks
Ashish Jain