Query Security Roles from SQL in AX 2012
It’s not something common, but in some cases it can be useful to check which security roles a user has by querying the database. The ideal option would be to use web services or something similar, with X++ code like the following (code extracted from chapter 10: “Licensing, Configuration and Security” in my book):
static void QueryRoles(Args _args)
{
SecurityRole securityRole;
SecurityUserRole securityUserRole;
while select securityUserRole
where securityUserRole.User == 'admin'
join securityRole
where securityRole.RecId == securityUserRole.SecurityRole
{
info(strFmt("%1", SysLabel::labelId2String(securityRole.Name)));
}
}
If we insist on doing this query from SQL Server we get a surprise. The SecurityUserRole table, which stores assigned roles for each user, exists in the AX transactional database, but the SecurityRole table, which stores the roles themselves, does not. It would be logical to think the table may exist in the model database (_model), but we do not find it there either.
After applying the trick I mentioned a few posts back, we find that the table is indeed in the model database, but abstracted behind a stored procedure, and it looks like this:
SELECT T1.NAME, T1.ALLOWPASTRECORDS, ...
FROM [XXXX_model].[dbo].SECURITYROLE_INLINEFUNC(N'en_us') T1
...
So, by tweaking the query a bit, we can get the same result as querying from X++:
But from SQL Server, and therefore from any external application:
Use with care ;)