Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 2 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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.
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.
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?
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:
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organization'
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'
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>'
select distinct tr.RoleId
from TeamRoles tr
join TeamMembership tms
on tms.TeamId = tr.TeamId
on sub.SystemUserId = tms.SystemUserId
where sub.SystemUserId = '<SystemUserID>')
order by pb.name
Business Applications communities