Detect who deleted a database based on the SQL Server default trace

find_who_deleted_sql_server_db.sql

With cteObjectTypes AS
	(
	    SELECT TSV.trace_event_id, TSV.subclass_name, TSV.subclass_value
	    FROM sys.trace_subclass_values AS TSV 
        JOIN sys.trace_columns AS TC 
          ON TSV.trace_column_id = TC.trace_column_id 
	    WHERE TC.[name] = 'ObjectType'
	),
	cteEventSubClasses AS
	(
	    SELECT TSV.trace_event_id, TSV.subclass_name, TSV.subclass_value
	    FROM sys.trace_subclass_values AS TSV 
        JOIN sys.trace_columns AS TC 
          ON TSV.trace_column_id = TC.trace_column_id 
	    WHERE TC.[name] = 'EventSubClass'
	)
SELECT TE.[name], I.ApplicationName, I.BigintData1, I.ClientProcessID, I.ColumnPermissions, I.DatabaseID, I.DatabaseName, I.DBUserName, I.Duration, I.EndTime, I.Error,	I.EventSequence,
	   convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
	I.FileName, I.HostName, I.IndexID, I.IntegerData, I.IsSystem, I.LineNumber, I.LoginName, I.LoginSid, I.NestLevel, I.NTDomainName, I.NTUserName, I.ObjectID, I.ObjectID2, I.ObjectName,
	Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
	I.OwnerName, I.ParentName, I.Permissions, I.RequestID, I.RoleName, I.ServerName, I.SessionLoginName, I.Severity, I.SPID, I.StartTime, I.State, I.Success, I.TargetLoginName, I.TargetLoginSid,
	I.TargetUserName, I.TextData, I.TransactionID, I.Type, I.XactSequence
FROM sys.traces T 
CROSS APPLY sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
                                       THEN SUBSTRING(path, 0, LEN(path) - CHARINDEX(N'\', REVERSE(path)) + 1) + N'\Log.trc' 
                                       ELSE T.[path] 
                                   END, T.max_files) I 
JOIN sys.trace_events AS TE 
  ON  I.EventClass = TE.trace_event_id 
LEFT JOIN cteEventSubClasses AS ESC 
  ON TE.trace_event_id = ESC.trace_event_id 
 AND I.EventSubClass = ESC.subclass_value 
LEFT JOIN cteObjectTypes AS OT 
  ON TE.trace_event_id = OT.trace_event_id AND
     I.ObjectType = OT.subclass_value
WHERE T.is_default = 1
  AND TE.NAME = 'Object:Deleted'
  AND OT.subclass_name = 'DB'
ORDER BY StartTime DESC