When was a Database Deleted and by whom | SansSQL

Monday, December 16, 2013

When was a Database Deleted and by whom

Here is a T-SQL query which gives information on who and when was a database deleted.

SELECT name AS EventName, DatabaseName, StartTime, LoginName
    FROM sys.traces T CROSS Apply 
 fn_trace_gettable(CASE WHEN CHARINDEX('_', [path]) <> 0
                           THEN SUBSTRING(PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
                           ELSE [path]
                         END, max_files) TT
    JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id
WHERE name like '%Deleted%' 
 AND ObjectName IS NULL 
 AND EventSubClass = 1
ORDER BY StartTime DESC 

No comments:

Post a Comment