I hear this one all the time: How do I find out who implemented object changes?
I also get: Can I see the query that caused object changes?
Let’s take a look at how you might audit who is doing what and how to your databases.
Object Changes in Extended Events
If you open up the New Session window for Extended Events in SSMS, the easy way to track down events is to simply type into the box. Here, we care about capturing object changes, so I’m going to simply type object, then scroll a bit:
There we are object_altered, object_created and object_deleted. These are the same events that you would see in Trace. Let’s use the GUI and take a quick look at what fields they capture:
That’s in the Configure window after you’ve selected the objects. It’s all pretty much what you can capture in Trace. Well, with the exception of ddl_phase and related_object_id. In Trace, ddl_phase is EventSubClass related_object_id is ObjectID2.
There you go. Extended Events or Trace Events, you can audit everything that was asked for. In fact, more in Trace. You can add the login and/or NT User.
Ah, but we can do that in Extended Events too. Slide over to the Global Fields (Actions) tab:
And there we have it. Parity.
What About the Query?
OK. That’s all cool. Trace & Extended Events work basically the same way to get everything we were asking for. However, what about the query?
Extended Events, but not Trace, has that too. Scroll down in the Actions:
We can capture the T-SQL as well. Here’s the output from a test I ran:
That’s it. It’s that easy. Now, we could get the T-SQL in Trace by adding other events, order the data, run queries, stuff like that. However, I’m lazy. This is much easier.
Conclusion
The purpose here is simple. Find out who made object changes in the database and show the query they used. You can answer that question pretty easily using Extended Events.
The goal of this series of posts is to show how Extended Events is so much more than just capturing query metrics. Let me know if it’s useful.
Also, I promised not to pick on Trace, and I don’t think I did. In fact, I wanted to show, for most of what you need in this scenario, Trace gets the job done, quick & easy. It just doesn’t do it all as easily. Here again, let me know if this crosses the line.