SQL Server: How to Find which user deleted the database in SQL Server

Introduction

In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server.

There are two different methods by which one can easily find who deleted the database in SQL Server. The first method is using builtin Schema Changes History Report. The second method is to load the SQL Server Default Trace into a table to see who deleted the database.

Steps to find who deleted the User database in SQL Server Using 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.

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.

Steps to Identify who deleted the user database using 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. For more information, see Importance of trace files in SQL Server.

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

How 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
('G:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\log_12.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 = 'MyTechMantra'
		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.

Conclusion

In this article we have seen how easily one can find out who deleted the user database in SQL Server with the help of in built SQL Server Schema Changes History Report.