follow site Hello Readers,
source site Before starting, let me give you some background about this problem. I used to support few servers which had more than 1000 small databases. Worst part was that many users had access to create/delete databases.
Many times, other users used to approach me asking “who has deleted my databases” It was a nightmare to support such servers. Then I found a good script which tells me who has deleted particular database and then I was able to find that user who intentionally or accidentally deleted the database.
http://thenovello.com/alfondie/elkos/4938 Here is the script which works with SQL Server 2008 and versions above.
http://mhs.se.loopiadns.com/evenemang/gokotterally-2010/?wc-ajax=get_refreshed_fragments SELECT DatabaseID,NTUserName,HostName ,LoginName,StartTime, DatabaseName FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE TE.trace_event_id =47 AND T.DatabaseName like 'test1' -------> Change your deleted database name here
go here I hope this helps!