How can I determine who dropped a DB in SQL Server 2005 (and when)?

Question

Is there a way to find out who dropped a database in SQL Server 2005 and the time / date that it was dropped?

Answer 1

If it was recent enough, it may still be in the default trace. The basic assumption will be that the DROP event will be the last event recorded for this database. So if someone dropped the database 'splunge' you should be able to catch it this way (again depending on how long ago it happened):

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 = 'splunge'
  ORDER BY StartTime DESC;