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

clip_image002[4]

Sample output:

clip_image002[6]

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.

clip_image002[8]

  • 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

clip_image002[10]

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 (5)

  1. Chance says:

    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

  2. Lakshmi says:

    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 succeeding

    I am troubleshooting the reason

    Checks:

    1. Checked the server log and error log both current and archived .No information was available expect the latest create database
    2. I checked the job modified date and created date both are of last month
    3. I checked the SP which is running under this job. The last modified date of this SP is also last month
    4. 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 these
    6. No related errors in the event viewer too

    I thought the database was never existed ,then how come the job completed successfully yesterday?

    Am I missing something else?

  3. Cole says:

    why cant we do something similar for creation and deletion of database user and logins?

  4. lromeijn says:

    please could you post option 3 without errors??? cant you use the Correct SQL syntax????

  5. granadaCoder says:

    Thank you. Method 3 was very helpful and easy to tweak.

Skip to main content