Security permission table

Hello, everyone,

I am looking for a SQL data table that would hold permission information for a certain menu item in AX 4.0. For example, I have a custom menu item (Production > Periodic > Qty Discrepancy). I would like to find out who (what user group) has access to it.

Thank you,

Serguei

Permission information is held in the AccessRightsList, but depending on what your looking for you may have to depend on other tables.

In this case you are interested in a menuitem, so the AccessRightsList with an inner join to the UserGroupInfo table should be all you need. If you’d have a table of field permission, however, you’d want to add a join to the SQLDictionary just to clarify the actual table and field names you are dealing with.

SQL code wise, you are probably looking for something like this:

DECLARE @DomainID nvarchar(3) = ‘myDomain’

,@SearchValue nvarchar(max) = ‘menuItemName’

,@AccessType int = 1 – 0 = None; 1 = View; 2 = Create; 3 = Edit; 4 = Full Control

SELECT arl.GROUPID
,ugi.NAME
,@SearchValue
,CASE (arl.ACCESSTYPE)
WHEN 0 THEN ‘None’
WHEN 1 THEN ‘View’
WHEN 2 THEN ‘Edit’
WHEN 3 THEN ‘Create’
WHEN 4 THEN ‘Full Control’
END

FROM ACCESSRIGHTSLIST arl
inner join USERGROUPINFO ugi
on ugi.ID = arl.GROUPID

WHERE arl.DOMAINID = @DomainID
and arl.RECORDTYPE >= 7
and arl.RECORDTYPE <= 16
and arl.ELEMENTNAME = @SearchValue
and arl.ACCESSTYPE >= @AccessType