Script to find which user deleted the user database in SQL Server

Use the below script to get the details of deleted/dropped databases. We can explicitly specify the name of the database which was deleted or get the output for all databases.

use tempdb

go

declare @enable int

select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = 'default trace enabled'

if @enable = 1 --default trace is enabled

begin

declare @d1 datetime;declare @diff int;declare @indx int ;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @temp_trace table (obj_name nvarchar(256) collate database_default,database_name nvarchar(256) collate database_default,start_time datetime,

              event_class int,event_subclass int,object_type int,server_name nvarchar(256) collate database_default,login_name nvarchar(256) collate database_default,

              application_name nvarchar(256) collate database_default,ddl_operation nvarchar(40) collate database_default);

select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx  = PATINDEX('%\%', @curr_tracefilename); set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

insert into @temp_trace select ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,ObjectType,ServerName,LoginName,ApplicationName,'temp'

from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2 

----------------------------------------------------------------------------------------------------------------------------------
and DatabaseName = 'SomeDB' -- <<<======Specify the name of the database here, else comment this line to get details of databases

----------------------------------------------------------------------------------------------------------------------------------

update @temp_trace set ddl_operation = 'CREATE' where event_class = 46

update @temp_trace set ddl_operation = 'DROP' where event_class = 47

update @temp_trace set ddl_operation = 'ALTER' where event_class = 164

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

select  start_time as Event_Time,Database_name,Server_name,Login_name,Application_name,

--SQLInstance,

              DDL_Operation from @temp_trace where object_type not in (21587)

order by start_time  desc

end

I have executed above script, not mentioned any DatabaseName in script it will show output for all and found one of my NEWDB Database was Dropped by some Login Account as shown below :

If you specify particular database Name and executed the script it will give you output for that Database only which is shown below :

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.