Malaysia: AX 2012 get list of security roles from SQL with label

[Blog bot]

Источник: http://axmas.blogspot.com/2016/06/ax...oles-from.html
==============

--List of security roles

select sr.RECID [Role Id], case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Role name]
from AXPRDDB_model.dbo.SecurityRole sr with (nolock)
left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'


--List of users with roles

select sur.USER_ [User Id], sr.AotName, case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Name], sur.VALIDFROM, sur.VALIDTO
from AXPRDDB.dbo.SECURITYUSERROLE sur with (nolock)
inner join AXPRDDB_model.dbo.SecurityRole sr with (nolock) on sur.SECURITYROLE = sr.RecId
left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when
left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'

--List of users with duties
select t.AOTNAME, case when left(t.Name, 1) = '@' then xr.Text else t.NAME end as [Role name], case when left(t.DESCRIPTION, 1) = '@' then xrd.Text else t.DESCRIPTION end as [Role name]
from SecurityRole s with (nolock)
inner join SecurityRoleTaskGrant g with (nolock) on s.RECID = g.SECURITYROLE
inner join SecurityTask t with (nolock) on g.SECURITYTASK = t.RECID
left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(t.NAME, 1) = '@' then (case when cast(SUBSTRING(t.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(t.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'
left join AXPRDDB_model.dbo.ModelElementLabel xrd with (nolock) on (case when left(t.DESCRIPTION, 1) = '@' then (case when cast(SUBSTRING(t.DESCRIPTION, 5, 7) as int) = xrd.LabelId and SUBSTRING(t.DESCRIPTION, 2, 3) = xrd.Module then 1 else 0 end) else 0 end) = 1 and xrd.Language = 'en_us'
where s.AOTNAME = 'LedgerAccountant'




Источник: http://axmas.blogspot.com/2016/06/ax...oles-from.html