Database Drops in SQL 2012

In the previous 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 the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

USE master;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
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 = 16964 -- '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 = 16964 -- 'DATABASE'
)
ADD TARGET package0.event_file(SET filename=N'C:DatabaseXEDBDeletedCreated.xel')
/* start the session */ALTER EVENT SESSION DBDeletedCreated 
ON SERVER 
STATE = START;
GO

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.

Enjoy!

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.