Who deleted my database?

Context: SQL Server RDBMS

Often times in database environments where more than one person possesses sysadmin role things could get messy. Especially when you realize an object (Database,SP, Table etc) was deleted and you have no idea by whom and when this object was deleted. Unfortunately SQL server error log

EXEC XP_READERRORLOG

doesn’t do a very good job of showing logs related to deleted objects. There are answers; you just need to know where to look. SQL server default trace comes to your rescue.  The TSQL below helps you identify who deleted the object and when. This can be used in the both events of knowing or not knowing the database/object name.

SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime
FROM
sys.fn_trace_gettable(CONVERT(VARCHAR(150),
( SELECT TOP 1
f.[value]
FROM    sys.fn_trace_getinfo(NULL) f
WHERE   f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete'
-- 47 Represents event for deleting objects.

 

The result will looks like this:

Trace_results

 

Thanks for reading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s