Determining who deleted a SQL Server database  is useful , particularly in sudden Production outages. I have witnessed many situations where an administrator is given elevated privileges for an upgrade – and for one reason or another the database is dropped.

Any decent DBA would ALWAYS make sure there is a suitable backup copy for the restore point. But accidents do occur and a method is required to quickly see who dropped the database and from which application

This query uses the default trace files . It’s assumed the trace files are still available and haven’t rolled over – which is the default setting. Read more detail about default traces

SELECT databasename,
SessionLoginName,
HostName, 
ServerName,
ApplicationName,
StartTime

FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) tf
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE EventClass = 47 and DatabaseName <> 'tempdb'
ORDER BY StartTime ASC;

Read More

SQL Server – Audit Backup\Restore Event with SQL Default trace ...

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

SQL Server – Last DML operation - SQL Server DBA