Audit who Dropped the Database

In the first article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through querying the default trace.  It is a good enough solution if you have not disabled the default trace.

On the other hand, what if you need to do something more flexible?  What if you wanted to track this down through a more robust tool such as extended events?  XEvents has what you need to be able to properly track these types of events.  In this article, I share how to use Extended Events to capture this kind of audit data.

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='DBDeletedCreated')
    DROP EVENT SESSION DBDeletedCreated 
    ON SERVER;
GO
CREATE EVENT SESSION DBDeletedCreated
ON SERVER
ADD EVENT sqlserver.object_created (
SET collect_database_name = (1)
ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text)
WHERE object_type = 'DATABASE'
),
ADD EVENT sqlserver.object_deleted(
SET collect_database_name = (1)
ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text)
WHERE object_type = 'DATABASE'
)
ADD TARGET package0.event_file(SET filename=N'C:DatabaseXEDBDeletedCreated.xel')
/* start the session */ALTER EVENT SESSION DBDeletedCreated 
ON SERVER 
STATE = START;
GO

There are a few things to note with this event session.  First is that I am trapping two separate events: sqlserver.object_deleted and sqlserver.object_created.  The next important note is that I have to enable a flag on both events to trap the database name.  That is done like this: SET collect_database_name = (1).  Last note is something that should be noticed after enabling the session and performing a couple of trials.  The events I am using will fire twice for every DROP or CREATE operation.  This happens due to the ddl_phase for each.  There is one event fired for the start of the event and then another event when the event commits or hits a rollback.  Because of this, I am outputting the ddl_phase in my query to read from the session data.  Let’s run a quick test and see how this data can be queried.

Also of importance is to note the file path for the output file. If the path does not exist or you do not have permissions to the directory, you will get an error message and the session will not create.

CREATE DATABASE XETestDB;
GO
DROP DATABASE XETestDB;
GO

Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

USE master;
GO
SELECT
event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(max)') AS DBName
,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') AS ObjName
,CASE event_data.value('(event/data[@name="ddl_phase"]/value)[1]', 'varchar(max)')
WHEN 0 THEN 'BEGIN'
WHEN 1 THEN 'COMMIT'
WHEN 2 THEN 'ROLLBACK'
END AS DDLPhase
,event_data.value('(event/data[@name="object_type"]/value)[1]', 'varchar(max)') AS ObjType
,event_data.value('(event/data[@name="transaction_id"]/value)[1]', 'varchar(max)') AS XactID
,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS SessionID
,event_data.value('(event/action[@name="nt_username"]/value)[1]', 'varchar(max)') AS ExecUser
,CONVERT(XML,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') )AS sql_text,
event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Client_AppName
FROM(
SELECT CONVERT(XML, t2.event_data) AS event_data
 FROM (
  SELECT target_data = convert(XML, target_data)
   FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s 
        ON t.event_session_address = s.address
   WHERE t.target_name = 'event_file'
    AND s.name = 'DBDeletedCreated') cte1
   CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent(FileTarget)
   CROSS APPLY  sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name', 'varchar(1000)'), NULL, NULL, NULL) t2)
    AS evts(event_data)
ORDER BY event_data.value('(event/@timestamp)[1]', 'varchar(50)');

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.

In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.  Enjoy!!