List view to see user list with corresponding access levels and roles ?

Welcome to the POB User Group Online Community! Forums PUG International List view to see user list with corresponding access levels and roles ?

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #3992
    ms
    Keymaster

    Is there a way to see in a list form a list of all users and their associated access levels ?
    Thanks

    • This topic was modified 3 years, 3 months ago by ms.
    #3994
    Stefan Reichelt
    Participant

    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?

    #3995
    ms
    Keymaster

    Hey Stefan, thanks for this. I have no coding experience, can you pls tell me where I should enter this.
    Thanks.

    #3996
    Stefan Reichelt
    Participant

    Do you have a SQL Management Studio installation anywhere? Maybe you have a DB admin who can support you. The code above is simple SQL, you just need to run it against your POB DB. There may be a chance to get such information using POB Explorers, but I didn’t try that and don’t have anything at hand right now.

Viewing 4 posts - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.