Script : Who deletes the databases?


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. Here is the script which works with SQL Server 2008 and versions above. 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 = 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

source site  

source link Output 2015-06-26 16-16-56  

go here I hope this helps!

Sachin Diwakar

Sharing creates magic!!

Facebook Twitter LinkedIn 

  1. Series : SQL DBA Cheatcode - DBA THINGS June 26, 2015 at 3:31 pm

    […] Script : Who deletes the databases? […]

%d bloggers like this: