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