Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

Is it possible to build a Power BI report on use security roles and their access

Posted on by 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.

Categories:
  • RE: Is it possible to build a Power BI report on use security roles and their access

    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

  • MikeC282 Profile Picture
    MikeC282 2,075 on at
    RE: Is it possible to build a Power BI report on use security roles and their access

    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?

  • Suggested answer
    RE: Is it possible to build a Power BI report on use security roles and their access

    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,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans