How do i Query the JIRA database to get the users and their project role for a particular Project.

[Shreyance Shaw]

SQL QUERY TO GET USERS AND THEIR PROJECT ROLES FOR A PARTICULAR PROJECT FROM JIRA DB

[Dieter · Accepted solution]

Somewhat slow due to the group by but works well for non nested groups :

select project.pkey as pkey,projectrole.name as rolename,roletypeparameter as roleuser from projectrole,projectroleactor,project where project.id = projectroleactor.pid and projectroleactor.projectroleid = projectrole.id and roletype = 'atlassian-user-role-actor'

union

select project.pkey as pkey,projectrole.name as rolename,cwd_membership.child_name as roleuser from cwd_membership,projectrole,projectroleactor,project where project.id = projectroleactor.pid and projectroleactor.projectroleid = projectrole.id and roletype = 'atlassian-group-role-actor' and membership_type='GROUP_USER' and parent_name=roletypeparameter

group by pkey,rolename,roleuser;

[Dieter]

if you need information about a special project just create a view:

create view project_role_user as <select from above>;

select * from project_role_user where pkey = <your project>