We have dozens of JIRA projects and groups. I'd like to be able to know for one group all the roles it's been given in all of the projects without going into each project to check.
Happy to have SQL query to answer this.
Thanks!
SELECT pname as "PROJECT", PROJECTROLE.name as "ROLE", ROLETYPEPARAMETER as "GROUP" FROM "PROJECTROLEACTOR" JOIN PROJECTROLE on PROJECTROLE.id=PROJECTROLEACTOR.PROJECTROLEID JOIN project on project.id=PROJECTROLEACTOR.pid where roletype='atlassian-group-role-actor' and ROLETYPEPARAMETER='jira-users'
You have to change the ROLETYPEPARAMETER to the name of the group you're searching for.
What database are you running and what version of JIRA. I've tested the query agains 6.2
I've used that plugin to execute the query and it works - https://marketplace.atlassian.com/plugins/com.atlassian.sysadmin.homedirectorybrowser
My results:
jira=# SELECT pname as "PROJECT", PROJECTROLE.name as "ROLE", jira-# ROLETYPEPARAMETER as "GROUP" FROM "PROJECTROLEACTOR" jira-# JOIN PROJECTROLE on PROJECTROLE.id=PROJECTROLEACTOR.PROJECTROLEID jira-# JOIN project on project.id=PROJECTROLEACTOR.pid jira-# where roletype='atlassian-group-role-actor' and ROLETYPEPARAMETER='jira-users'; ERROR: relation "PROJECTROLEACTOR" does not exist LINE 2: ROLETYPEPARAMETER as "GROUP" FROM "PROJECTROLEACTOR"
Did I take the query too literally?
v6.2.1. Postgresql
A workaround would be to find a user that is part of that role, go to the View Project Roles and then Edit Project Roles for User and it will show if a group is mapping that user to a project role and will name the group.
Look at Atlassian Dock below.
https://confluence.atlassian.com/jirakb/retrieve-a-list-of-users-assigned-to-project-roles-in-jira-server-705954232.html
It looks like you're new here. Sign in or register to get started.
![Photo of Boris Georgiev [Appfire]](https://us.v-cdn.net/6038660/uploads/avatars/10/f7/n10f770357a7337c5246663f8369d9054.png)
