How to get a list of all users based on directory groups who has access to restricted pages

[yongwa]

I am looking for database query to get a list of all users based on internal groups who has access to restricted pages and also to list of all users who has access to unrestricted pages. So what would be the query to retrieve this info from the database. 

[Murat Seven]

Hi @yongwa  and welcome to the Atlassian Community!

Could you try this SQL?

select u.id as "User Id", a.user_key as "App User Key", u.lower_user_name as "Lower Username", u.active as "User status", ud.id as "User Directory Id", ud.directory_position as "User Directory Order", ud.directory_name as "User Directory Name", ud.active as "User Directory Status", g.id as "Group Id", g.group_name as "Group Name", gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status"
from cwd_user u
left join app_user a on a.lower_user_name = u.lower_user_name
join cwd_directory ud on ud.id = u.directory_id
left join cwd_membership m on m.child_id = u.id
left join cwd_group g on g.id = m.parent_id
left join cwd_directory gd on gd.id = g.directory_id
order by ud.directory_position ASC; 

Additionally, you can follow this link and edit the query as needed.

Hope this helps. If the issue is resolved, you can vote and accepted for this comment.

Best,

Murat Seven