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)

 

Author:

Raghavendra Srinivasan , Support Engineer, Microsoft India GTSC

Reviewed by:

Balmukund Lakhani, Support Escalation Engineer, Microsoft India GTSC

Comments

• Anonymous
  October 19, 2015
  Currently my servers have about 12-14 hours of history, how can I extend this so that it spans a weekend. I have users that come in on monday and say someone (not them of course) deleted their DB on Saturday at noon. Thanks, Chance

• Anonymous
  May 17, 2016
  Issue : one job which runs for every 5 minutes failed today (completed successfully yesterday) with error one of the object database.dbo.Table was not found.The database was not found on the server. Immediately Application team created the DB to resolve the issue and then the job started succeedingI am troubleshooting the reasonChecks: 1. Checked the server log and error log both current and archived .No information was available expect the latest create database2. I checked the job modified date and created date both are of last month3. I checked the SP which is running under this job. The last modified date of this SP is also last month4. daily running maintenance jobs are not considering this database(found this in the log file of maintenance job)5. Checked for any hardware failures, some other databases were also existing on the same drive and no issues with these6. No related errors in the event viewer tooI thought the database was never existed ,then how come the job completed successfully yesterday?Am I missing something else?

• Anonymous
  February 21, 2017
  The comment has been removed

• Anonymous
  February 22, 2017
  The comment has been removed

• Anonymous
  June 01, 2017
  Thank you. Method 3 was very helpful and easy to tweak.