SQL QUERY TO GET USERS AND THEIR PROJECT ROLES FOR A PARTICULAR PROJECT FROM JIRA DB
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;
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.