SQL statement behind "Assignee"-Possibilities Edited

Hello!

I am trying to find out which users have an JIRA Software access, even though they haven´t logged in yet - so they are possible assignees.

It is similar to the query which shows the possible users in the field "Assignee".

I tried some SQL Statements. Either i have all users (even though they are not allowed to have access) or only the users which have logged in yet.

  

Can somebody help me please :-)

 

Due to an upload error, there are two questions for the same topic

https://community.atlassian.com/t5/Jira-questions/SQL-statement-behind-quot-Assignee-quot-Possibilities/qaq-p/673502

 

2 answers

This widget could not be displayed.
Jeremy Mooiman Community Champion Nov 16, 2017

Dear Dunja,

In JIRA 7.0 or above, the different licensed users in the Database have now been fully separated. You can use the following SQL against your Database in order to receive a list of all licensed Jira Software users;

SELECT DISTINCT u.lower_user_name
FROM   cwd_user u
       JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN licenserolesgroup lrg
         ON Lower(m.parent_name) = Lower(lrg.group_id)
       JOIN cwd_directory d
         ON m.directory_id = d.id
WHERE  d.active = '1'
       AND u.active = '1'
 AND license_role_name = 'jira-software';

 Also; A licensed Jira Software user does not have to be a user to which issues can be assigned. This is regulated in the Permission Scheme, you can limit it to certain groups or roles.

Friendly Regards,

Jeremy Mooiman

@Dunja E- please, don't edit questions when you have totally different new ones to ask.  Jeremy's answer was good for most of your original question, but now makes no sense for the new question.

If you have a new question, raise it new, don't edit an old one.

I've put the old one back so it does make sense.

(I came here to add something to tell you that the SQL for "possible assignee" is hideously long and you don't want to try it)

Hello Nic,

 

thank you for your answer. I found out, that my question was posted two times because of an error of the homepage. Because of this I used one of them to add my second question..

 

Thank you for your answer!

Ok, it's better not to do that, just answer a question with something like "added in error" and mark it correct.

Thanks for tidying up!

This widget could not be displayed.

Hello Jeremy, 

thank you for your answer and help. The SQL-Statement works fine but it doesn`t include users which haven´t logged in yet. 

Friendly regards, 

Dunja

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday 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...

174 views 1 3
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