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
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;
<span>SELECT</span> <span>DISTINCT</span> u<span>.</span>lower_user_name <span>FROM</span> cwd_user u <span>JOIN</span> cwd_membership m <span>ON</span> u<span>.</span>id <span>=</span> m<span>.</span>child_id <span>AND</span> u<span>.</span>directory_id <span>=</span> m<span>.</span>directory_id <span>JOIN</span> licenserolesgroup lrg <span>ON</span> Lower<span>(</span>m<span>.</span>parent_name<span>)</span> <span>=</span> Lower<span>(</span>lrg<span>.</span>group_id<span>)</span> <span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> m<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id <span>WHERE</span> <span>d</span><span>.</span>active <span>=</span> <span>'1'</span> <span>AND</span> u<span>.</span>active <span>=</span> <span>'1'</span> <span>AND</span> license_role_name <span>=</span> <span>'jira-software'</span><span>;</span>
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!
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
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!
It looks like you're new here. Sign in or register to get started.
