How to determine who deleted a SQL Server database

22 February,2014 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to determine who deleted a SQL Server database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer