Find which groups have access to projects in Jira Data Center
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Permission to access a project can be tied to different types of user access.
From the Jira Database, we can determine which users and groups were assigned to Project Roles specifically.
SQL query to find project role association
This query covers only project roles. Groups may also be assigned to a project's permission scheme as well.
Please refer to Managing project permissions for more information.
You may run the following SQL query to find out the Users/Groups associated with Project Roles in a Project.
This query was written and tested only on PostgreSQL and MySQL databases, you may need to rewrite it accordingly to suit other supported databases.
The query specifically returns which Groups are assigned to a Project role and to which Project.
SELECT project.pkey as pkey,project.pname as project_name,projectrole.name as project_role_name,cwd_membership.parent_name as groupuser
FROM cwd_membership,projectrole,projectroleactor,project
WHERE project.id = projectroleactor.pid
AND projectroleactor.projectroleid = projectrole.id
AND (cwd_membership.parent_name = projectroleactor.roletypeparameter or cwd_membership.child_name = projectroleactor.roletypeparameter)
AND roletype = 'atlassian-group-role-actor'
AND membership_type='GROUP_USER'
AND parent_name=roletypeparameter
GROUP BY pkey,project_name,project_role_name,groupuser;