SQL for list of users JIRA project

Hi

 

Please help....can i get a sql for to retrieve list of users in a JIRA project

3 answers

0 votes

Depends on your user directory, but there's no easy SQL to get it even in the internal directory.

First, what do you define as a "user of a project"?  Someone who can see it?  Or someone who can create issues in it?  Or they can be assigned issues?  And so-on.

Then you need to read your permission scheme to find out the rules set to determine that "is a user".  For example, if your rule is "can create issues, or can be assigned issues", then you need the rules set for "Create" and "Assignable user".

Those rules could be quite simple - "group X" or "user Y", but it's quite likely that they are more flexible than that, so they'll say "role Z" or "reporter" or or or or...

So you then need to interpret those rules to get back to a set of users.  If it's "group X", then you can simply read cwd_membership for the user ids in the group.  But if it's "role X", then you'll need to read the project role for the users in that role and the groups, then go into the groups to get the users.

In short, you have a big task in front of you, and we can't even begin to help you with raw SQL until you have a hard rule for "is a user in a project"

Thanks for your response Nic

 

Any user who has permissions on the project (as Admin or anyother) is how i would classify a user.

Ok, that's good because it's clear, but potentially complex because you'd need to read the entire permission scheme for every rule. I'd dodge that and just look at the "browse" permission in the scheme if you need a simple view - it's technically wrong, but it's rare that you'd have a user who can do things in a project but not actually see the issue! So, you'll need to read schemepermission to get the list of groups, roles, reporter, assignee, customfield etc for "Browse project", then read the appropriate tables for each of those - reporter and assignee are on jiraissue, the groups are in cwd_membership, the roles are in projectrole, join that to projectroleactor to get the lists of groups and users in the roles etc. Maybe it's easier to ask *why* you're trying to do this? And maybe why you're choosing the hard way?

why - that is an interesting question.  Our current instance of JIRA is used by few departments and as we are planning to move over to cloud, we have been asked by folks who control the money, to identify user, project so that we could get funding from all stake holders

The SQL for JIRA plugin can answers to this question in an straightforward way:

The query below shows all the actors (users playing a role) in a project

SELECT * FROM PROJECTROLEACTORUSERS

Therefore, you might want to run:

SELECT * FROM PROJECTROLEACTORUSERS WHERE ROLENAME='jira-developers'

Furthermore, SQL for JIRA is fully integrated with JIRA security and permissions, so each users would see his roles only. Of course, JIRA administrators would see the roles for everybody on each project.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 18, 2018 in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

26,507 views 2 7
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you