Home > dynamics ax 2012 > sql query to get security roles vs menu items

sql query to get security roles vs menu items


friends, sometimes we got trouble that looking security roles in link menu items,

i have some sql queries in database that may help,
you will get SecurityRoles — duty — privilage — menuitems

and with some modification on xls, you can join the license type (i have posted the article before this on) so you can know the license type of each security roles

select
	a.aotname,
	a.name,
	c.AOTNAME as taskname,
	CASE when c.TYPE=1 then 'duty' else 'privilage' end as tasktype,
	e.AOTNAME as privilagename,
	g.NAME,
	i.NAME
from
	securityrole a
	inner join SECURITYROLETASKGRANT b
		on b.SECURITYROLE=a.RECID
	inner join SECURITYTASK c
		on c.RECID=b.SECURITYTASK
	left join SECURITYSUBtask d
		on d.SECURITYTASK=b.SECURITYTASK
	left join securitytask e
		on e.recid=d.securitysubtask
	left join SECURITYTASKENTRYPOINT f
		on f.SECURITYTASK=d.SECURITYSUBTASK or f.SECURITYTASK=b.SECURITYTASK
	left join SECURABLEOBJECT g
		on g.RECID=f.ENTRYPOINT
	left join SYSMODELELEMENT h
		on h.name = g.NAME
	inner join SYSMODELELEMENTTYPE i
		on i.recid=h.ELEMENTTYPE and i.RECID in (1,2,3)
where
	a.AOTNAME like 'HPG%' or a.AOTNAME like 'IV%'
--use this filter for specific roles
order by
	a.AOTNAME, tasktype, taskname
	;
  1. No comments yet.
  1. No trackbacks yet.

Please kindly Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: