Script : Who deletes the databases?


Hello Readers,

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 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

 

Output

2015-06-26 16-16-56

 

I hope this helps!

,

One response to “Script : Who deletes the databases?”