Welcome to the POB User Group Online Community! › Forums › PUG International › List view to see user list with corresponding access levels and roles ? › Reply To: List view to see user list with corresponding access levels and roles ?
Probably not 100% of what you want, but this is at least something to begin with:
declare @RoleID as nvarchar(255) = 'Normal' select r.id as 'Role', 'User' as 'Type', u.id as 'ID' from Pob_Role r left join User_Pob_Role ur on r.pob_role_pk=ur.pob_role_pk left join User_Table u on ur.user_pk=u.user_pk where u.delete_date is null and r.id = @RoleID union all select r.id as 'Role', 'User Group' as 'Type', ug.id as 'ID' from Pob_Role r left join User_Group_Pob_Role ugr on r.pob_role_pk=ugr.pob_role_pk left join User_Group ug on ugr.user_Group_pk=ug.user_group_pk where ug.delete_date is null and r.id = @RoleID order by 2,3
It allows you to see all users and user groups that are assigned to role @RoleID. You can modify the statement to show you all combinations of users, groups and roles together, and with tools like Excel Pivot or Power BI, you can create fancy charts to see who can do what.
Still anything missing?