SQL statement behind "Assignee"-Possibilities

[Dunja E]

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

[Jeremy M]

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

[Nic Brough -Adaptavist-]

@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)

[Dunja E]

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!

[Nic Brough -Adaptavist-]

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!

[Dunja E]

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