How to list all groups memberships in Confluence using a SQL query
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
For auditing or administration purposes, an administrator may want to export a list of all the users and their associated group memberships. This can be done via a SQL query.
Solution
Use the following SQL query:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT d.directory_name AS DirectoryName, d.directory_type AS DirectoryType, g.group_name AS Group, u.email_address AS Email, u.user_name AS username, u.display_name as displayname
FROM
cwd_user u
JOIN cwd_directory d
ON
u.directory_id = d.id
JOIN cwd_membership m
ON
u.id = m.child_user_id
JOIN cwd_group g
ON
g.id = m.parent_id
ORDER BY u.user_name ASC, d.id, g.group_name ASC; Notes:
The
cwd_usertable stores information about the users’ profiles.The
cwd_grouptable stores the group namesThe
cwd_membershiptable stores the membership between groups and users.Group memberships are associated with the directory where the user comes from. If the same user appears in multiple user directories, they could have different group memberships. So, the
cwd_directorytable is useful to help us identify that.
Was this helpful?