SQL Server Trace not capturing all Audit Add DB User events

Question

In the SQL Server Audit Add DB User Event Class, there are four Event Sub Classes defined:

1. Add
2. Drop
3. Grant database access
4. Revoke database access

(MS documentation found here)

When I set up SQL Server Profiler to trace the Audit Add DB User Event Class, it only seems to capture events with a subclass of 3 or 4, and not 1 or 2.

To test the trace, I am using the following SQL statements:

CREATE USER testuser FOR LOGIN testlogin;
DROP USER testuser;

When I run these statements in SQL Server Management Studio, SQL Server Profiler displays two Audit Add DB User Events, one with EventSubClass 3 (Grant database access) and one with EventSubClass 4 (Revoke database access), but does not display anything for EventSubClass 1 (Add) or EventSubClass 2 (Drop).

From what I can tell, all three even subclasses should be covered by the SQL statements used above. Is there something additional that needs to be configured in order to capture these event subclasses?

Answer 1

The old trace functionality has been deprecated since 2012. I did some testing and depending on what commands I executed I could get 2. But I never managed to get 1. If you look at the documentation for this event class, you'll see that it is documented to provide information when you use the ancient procedures sp_adduser, sp_dropuser, etc. But even when doing that it seems a bit flaky.

Sure, one could report this to MS, but they will (most likely) just say that you should use a technology which isn't deprecated. I.e., Extended Events. I very much doubt that MS will pour any resources into fixing this, even if that would consider this to be a bug in the first place. So, my recommendation will be the same: Look into Extended Events instead.

Here's a blog I wrote about "getting into" XE: http://sqlblog.karaszi.com/tips-for-getting-started-with-extended-events/