How to Find which user deleted the databases in SQL Server

In this blogpost we will see and learn how to quickly identify the user who deleted the user database in SQL Server. Mainly two different methods what I know as far by which one can easily find who deleted the database in SQL Server.

Method 1 : Using builtin SQL Server Schema Changes History Report

1) Open SQL Server Management Studio and Connect to the SQL Server Instance.

2) Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet.

SQL Server Schema Changes History Report

3) This will open up Scheme Changes History report which will have the details about who deleted the SQL Server Database along with the timestamp when the database was deleted. Refer the below snippet for more information.

Method 2 : Using Default Trace Files

The SQL Server Default Trace file gives very useful information to a DBA to understand what is happening on the SQL Server Instance.

Execute the below query to find the default path of 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

Script to Load SQL Server Trace File in SQL Server Table

Execute the below script to load the default trace file content in a temporary table to read the relevant information with respect to who deleted the user database on the instance of SQL Server. If you don’t find the relevant information in the latest trace file then it is recommended to load the data from all the available trace files on the server to explore the information.

USE tempdb
GO

IF OBJECT_ID('dbo.TraceTable', 'U') IS NOT NULL
        DROP TABLE dbo.TraceTable;

SELECT * INTO TraceTable
FROM ::fn_trace_gettable
('D:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_1.trc', 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 = 'ImJhaChandan'
                 AND (EventClass = 46 /* Event Class 46 refers to Object:Created */
                         OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */
GO

From the above snippet you could see that the event class 46 represents the database creation time along with the user who created it and event class 47 represents the database deletion time along with the user who deleted the database.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .