2,075
Hi all,
Quick question. I know there's a couple of tools in the XRM toolbox that allows you check which security roles have access to which entity, who has these security roles and allows you to export a csv file. It's all a bit of a manual process and just wondering whether it's possible to pull security role and table access and user security access into a Power BI report so that when I audit access for users it doesn't have to be a manual process.
I have this query for Onprem environment, but eventually could be an inspiration for you.
Retrieve privileges for an user based on user's security roles and user's team security roles as well:
select pb.name,
SecurityLevel =
CASE rp.PrivilegeDepthMask
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organization'
END,
AccessLevel =
CASE pb.AccessRight
WHEN 1 THEN 'READ'
WHEN 2 THEN 'WRITE'
WHEN 4 THEN 'APPEND'
WHEN 16 THEN 'APPENDTO'
WHEN 32 THEN 'CREATE'
WHEN 65536 THEN 'DELETE'
WHEN 262144 THEN 'SHARE'
WHEN 524288 THEN 'ASSIGN'
END
from roleprivileges as rp
join privilegebase as pb on rp.privilegeid = pb.privilegeid
where rp.RoleId in (
select distinct sur.RoleId
from SystemUserRoles sur
join SystemUserBase sub
on sub.SystemUserId = sur.SystemUserId
where sub.SystemUserId = '<SystemUserID>'
union
select distinct tr.RoleId
from TeamRoles tr
join TeamMembership tms
on tms.TeamId = tr.TeamId
join SystemUserBase sub
on sub.SystemUserId = tms.SystemUserId
where sub.SystemUserId = '<SystemUserID>')
order by pb.name
Thanks bud. I know I can pull out static csv files but is there a way to directly access the roles and privilege tables/entities in Dynamics? I'm thinking using the Power Query (M) Builder in the XRM toolbox to pull out those tables. I've already created a BI report that contains the name and email of several AD security groups that need to be matched to certain entity access. BI seems to connect with AD fine.
I've had a quick looking using Fetch XML builder and have found the roles entity which seems to contain the security role values. There also seems to be a privilege entity, privilegeobjecttypecodes. Just unsure how this all joins together. Basically if I pull them all into the BI report what's the cardinality/data modelling is it even possible? Hoping someone out there already tried doing this via power BI.
Does it actually need code to pull? I'm assuming all the access data is stored in tables within Dynamics. If I'm just after the ability to read it can I access it using Fetch XML?
Hello MikeC282,
Hope you are well.
You need something pulling the security role info from Dynamics 365.
Eventually you can create a SDK code and run it periodically or even use these XRM toolbox plug-ins as well.
Later you can consume the output in Power BI to prepare an user friendly report.
Regards,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,558
Super User 2024 Season 2
#2
Martin Dráb
228,645
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (