Microsoft Dynamics AX 2012 — Which user is responsible for the running query?

Not so focused on development itself, this post has a very specific purpose:

In a SQL Server database where Microsoft Dynamics AX 2012 is running, all connections originate from AOS and, consequently, a user normally called “svc.aos”. Thus, it is difficult to collect all the “context” related to the database and responsible user at once.

As a context, we can understand things like: query, execution plan and duration.

There is a stored procedure that, in a synthetic way, is capable of presenting various information with a simple execution. It is sp_WhoIsActive.

However, as it is only focused on SQL Server and not on Dynamics 2012 itself, I built a change that can be seen here on my github that is responsible for adding an extra column that represents the existing user within UserInfo that is linked to particular database session.

Example:

Result:

The “dax” column reflects the name of the existing user in the UserInfo of the database specified in the “get_dax_database” parameter.

Due to space limitations, the image does not have other important columns such as the execution plan itself.

Even so, it is possible to check which user is causing, in this example, a lock.