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.

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.

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.

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!!

5 thoughts on “Audit who Dropped the Database”

  1. Are these commands specific to SQL 2012? I am on 2008r2 std and receive a set of errors running the first part.

    Msg 25623, Level 16, State 1, Line 1
    The event name, “sqlserver.object_created”, is invalid, or the object could not be found
    Msg 15151, Level 16, State 1, Line 17
    Cannot alter the event session ‘DBDeletedCreated’, because it does not exist or you do not have permission.

  2. I’m on SQL 2012 SP2 and getting the following error:

    Msg 25713, Level 16, State 23, Line 1
    The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.
    Msg 15151, Level 16, State 1, Line 17
    Cannot alter the event session ‘DBDeletedCreated’, because it does not exist or you do not have permission.

    Please help on this

    1. For SQL Server 2012 you will need to use the Map Key value instead of the value name.
      In this case the value is 16964.
      So do
      WHERE object_type = 16964
      instead of
      WHERE object_type = ‘DATABASE’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.