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

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

In one of the recent scenarios we noticed that a user database was deleted and customer wanted to which user has dropped the database. We know that multiple user had full access on that database.
In this post I’ll be sharing the steps to find the details of user who drop the database.

Method-1

• Connect the SQL Server instance using management studio
• Right-click on the instance and select

“Reports”—“Standard Reports”—“Schema Changes History”

• We get a report of schema changes for all databases from which we can get the user account which was used to delete/drop the database

Sample output:

Note: This report doesn’t contain the details of application or the server from which the DROP statement was executed.

Method – 2:

• Get the location of SQL Errorlog using one of the below commands

sp_readerrorlog 0,1,'Logging SQL Server messages in file'

go

Sample output:

LogDate ProcessInfo Text

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

2015-01-09 15:31:31.330 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Log\ERRORLOG'.

--or

select SERVERPROPERTY('errorlogfilename')

go

Sample output:

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

C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Log\ERRORLOG

• Look for default SQL trace files and open the trace files which points to the time of issue. “Log_ 111.trc” for example.

• Make a copy of the file in same or different location “log_111 - Copy.trc”
• We can now manually open this file in SQL Profiler and search for keywork “Object:Deleted” or load it to SQL table and use T-SQL query to get the details. Here I’m providing the steps for T-SQL

--To load the trace to SQL table

use tempdb

go

SELECT * INTO trace_table FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Log\log_111 - Copy.trc', default)

go

--Get the details of the deleted database

--Change the value of the database name to the one which was deleted

select DatabaseID,DatabaseName,LoginName,HostName,ApplicationName,StartTime from tempdb.dbo.trace_table

where DatabaseName = 'somedb' and eventclass =47 -- 47 is Object:Deleted Event Class

Sample output:

DatabaseID DatabaseName LoginName HostName ApplicationName StartTime

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

26 SomeDB SQL_User1 Client1 Microsoft SQL Server Management Studio - Query 2015-01-14 12:43:46.630

26 SomeDB SQL_User1 Client1 Microsoft SQL Server Management Studio - Query 2015-01-14 12:43:46.630

(2 row(s) affected)

From the above we can clearly say that  “SQL_User1” user deleted the database from machine “Client1” using SSMS at the above mentioned time.

 

Method – 3

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

 

Note:- The above query is the modified version of query which is executed in the background when we use SQL Server Reports (Method-1)