SQL statement behind "Assignee"-Possibilities

Dunja E November 16, 2017

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

1 vote
Jeremy M
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 16, 2017

@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 November 16, 2017

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 16, 2017

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!

0 votes
Dunja E November 16, 2017

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