Who issued " DROP Database " command ?

Sometimes it is hard to track who dropped a SQL Server database,  especially when you are in a  large shop managing several databases. It should have been dropped by one of your team members as part of any decommission request or the application users themselves; who have enough privileges on the DB or Server. Here is one quick script I use. If we are lucky enough, we will find an entry in the default trace.😎

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + '\Log.trc' --'
FROM sys.traces
WHERE is_default = 1;

SELECT TOP 1 LoginName
	,HostName
	,ApplicationName
	,StartTime
	,TextData
FROM sys.fn_trace_gettable(@FileName, DEFAULT) AS gt
WHERE EventClass = 47
	AND DatabaseName = 'Pass DB Name here'
ORDER BY StartTime DESC;

Hope it helps !!