[Mark Schurmann]

We are implementing D365 for Operations.

I’m trying to write a query to give me an overview of the security role duties we have setup for our users.  I’m having trouble figuring out how to join the SecurityRole to the SecurityDuty.  Here is what I have so far.  I would expect a table like SecurityRoleSecurityDuty but nothing is jumping out at me.

select sr.name as SecurityRole,ssr2.name as SecuritySubRole, ssr2.[Description],sd.Name, sd.[Description]
from SecurityRole sr
join SecuritySubRole ssr on ssr.SecurityRole = sr.RecID
join SecurityRole ssr2 on ssr.SecuritySubRole = ssr2.RecID
join ??? srsd on srsd.SecurityRole = ssr2.RecID
join SecurityDuty sd on sd.RecID = srsd.SecurityDuty
where sr.name like ‘APCO%’
Order by sr.Name, ssr2.name

Can anyone fill in the ???

I think this would be a handy report for auditors

Thanks.

——————————
Mark Schurmann
Automobile Protection Corp
Norcross GA
——————————

[Scott Morley]

Mark,
Taking out the sub roles (there aren’t very many in my system), I got this to work:

select sr.name as SecurityRole,sd.Name, sd.[Description]
from SecurityRole sr
join SECURITYROLEDUTYEXPLODEDGRAPH srsd on srsd.SecurityRole = sr.RecID
join SecurityDuty sd on sd.RecID = srsd.SecurityDuty
where sr.name like ‘Aud%’
Order by sd.Name

Hope that helps!

——————————
Scott Morley
Principal Application Architect
OneNeck IT Solutions
Bend OR
——————————
——————————————-

[Chris Vaughn]

Mark – Here is a script I used to get all roles and their associated duties. Be aware this is for ALL security roles whether they’re assigned or unassigned.

/****** Roles with Associated Duties ******/

SELECT
   SECURITYROLEDUTYEXPLODEDGRAPH.SECURITYROLE
   ,SECURITYROLE.NAME AS ‘ROLE’
   ,SECURITYROLEDUTYEXPLODEDGRAPH.SECURITYDUTY
   ,SECURITYDUTY.NAME AS ‘DUTY’
   ,SECURITYDUTY.DESCRIPTION AS ‘DESCRIPTION’
FROM [DBO].[SECURITYROLEDUTYEXPLODEDGRAPH]
—
   JOIN DBO.SECURITYROLE ON (SECURITYROLE.RECID = SECURITYROLEDUTYEXPLODEDGRAPH.SECURITYROLE)
—
   JOIN DBO.SECURITYDUTY ON (SECURITYDUTY.RECID = SECURITYROLEDUTYEXPLODEDGRAPH.SECURITYDUTY)
— WHERE SECURITYROLE = ‘242’
 ORDER BY ROLE, DUTY

——————————
Chris Vaughn
Business Analyst – Contura Energy
Bristol TN
——————————
——————————————-

[Mark Schurmann]

Thanks for the help. My Model table is currently empty.  Not sure if this is because of a change in D365.

The magic I was looking for was the SECURITYROLEDUTYEXPLODEDGRAPH table.  Here are the queries I came up with to solve my problem.

–Duties assigned to roles
select sr.name as SecurityRole,ssr2.name as SecuritySubRole, ssr2.[Description] as SecurityRoleDescription,sd.Name DutyName, sd.[Description] as DutyDescription
from SecurityRole sr
left outer join SecuritySubRole ssr on ssr.SecurityRole = sr.RecID
join SecurityRole ssr2 on ssr.SecuritySubRole = ssr2.RecID
join SECURITYROLEDUTYEXPLODEDGRAPH srsd on ssr2.RecID = srsd.SecurityRole
join SecurityDuty sd on sd.RecID = srsd.SecurityDuty
Order by SecurityRole, SecuritySubRole,DutyName, DutyDescription

— Users assigned to Roles
select sr.Name as SecurityRole, u.Name as UserName
from  SecurityRole sr
join SecurityUserRole sur on sr.RecID = sur.SecurityRole
join userinfo u on u.ID = sur.USER_
Order by sr.Name, u.Name

——————————
Mark Schurmann
Automobile Protection Corp
Norcross GA
——————————
——————————————-

[Sherry Moran]

We’re on AX2012 R2 and I created the following view, I found that I had to join data from model to get everything I needed.

CREATE VIEW [dbo].[KEY_VIEW_UserSecurityRoles]

AS

SELECT SUR.USER_, U.NAME as UserName, MSR.NAME AS RoleName, CASE WHEN msr.LABEL LIKE ‘@%’ THEN msr.NAME ELSE msr.label END AS KeyName,

CASE WHEN SUR.ASSIGNMENTSTATUS = 1 THEN ‘GRANTED’ WHEN SUR.ASSIGNMENTSTATUS = 2 THEN ‘DENIED’

ELSE CAST(SUR.ASSIGNMENTSTATUS AS varchar) END AS ASSIGNMENTSTATUS,

CASE WHEN SUR.ASSIGNMENTMODE = 1 THEN ‘MANUAL’ ELSE ‘AUTOMATIC’ END AS ASSIGNMENTMODE,

U.ID, U.RECID, CASE WHEN U.ENABLE = 0 then ‘DISABLED’ ELSE ‘ENABLED’ END AS STATUS

FROM dbo.SECURITYUSERROLE AS SUR LEFT OUTER JOIN

AX2012_LIVE_model.dbo.ModelSecurityRole AS MSR ON SUR.SECURITYROLE = MSR.ROLEHANDLE LEFT OUTER JOIN

dbo.USERINFO AS U ON SUR.USER_ = U.ID

——————————
Sherry Moran
SR Business Systems Analyst
Key Technology, Inc
Walla Walla WA
——————————
——————————————-

[Lloyd Dehn]

Updated the SQL to use the label text for the security role name when a label is used.

SELECT sur.USER_ AS UserId,
u.NAME AS UserName,
msr.NAME AS RoleName,
CASE
WHEN msr.LABEL LIKE ‘@%’ THEN mel.Text
ELSE msr.LABEL
END AS RoleLabel,
CASE
WHEN sur.ASSIGNMENTSTATUS = 1 THEN ‘GRANTED’
WHEN sur.ASSIGNMENTSTATUS = 2 THEN ‘DENIED’
ELSE CAST(sur.ASSIGNMENTSTATUS AS varchar)
END AS AssignmentStatus,
CASE
WHEN sur.ASSIGNMENTMODE = 1 THEN ‘MANUAL’
ELSE ‘AUTOMATIC’
END AS AssignmentMode,
u.ID,
u.RECID,
CASE
WHEN u.ENABLE = 0 THEN ‘DISABLED’
ELSE ‘ENABLED’
END AS STATUS
FROM dbo.SECURITYUSERROLE AS sur
LEFT OUTER JOIN AX2012Prod_model.dbo.ModelSecurityRole AS msr ON sur.SECURITYROLE = msr.ROLEHANDLE
LEFT OUTER JOIN AX2012Prod_model.dbo.ModelElementLabel AS mel on mel.Module = msr.LABELMODULE and mel.LabelId = msr.LABELID and mel.Language = ‘en_us’
LEFT OUTER JOIN dbo.USERINFO AS u ON sur.USER_ = u.ID

——————————
Lloyd Dehn
Dynamics AX Lead Developer
Braun Intertec
Bloomington MN
——————————
——————————————-