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)
No comments:
Post a Comment