How can I find all users and their group memberships?
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As an administrator, you would like to get a list of all Confluence users and the group memberships they belong to.
You may also be interested in getting a list of those users who do not belong to any groups. How can we find these users?
Environment
Confluence 7.19.X and Confluence 8.X
Resolution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Users list and groups membership
Run the following query from your Confluence database:
SELECT cu.id as user_id
,cu.user_name
,cg.group_name
,cd.directory_name
FROM cwd_user cu
INNER JOIN cwd_membership cm ON (cu.id = cm.child_user_id)
INNER JOIN cwd_group cg ON (cm.parent_id = cg.id)
INNER JOIN cwd_directory cd ON (cu.directory_id = cd.id)Users without any group association
Run the following query from your Confluence database:
SELECT *
FROM cwd_user u
WHERE u.id
NOT IN (
SELECT u.id
FROM cwd_user u
JOIN cwd_membership ON u.id = cwd_membership.child_user_id
JOIN cwd_group ON cwd_membership.parent_id = cwd_group.id)