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;
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |