List all the jira proejcts with its groups and users

[Reneesh Kottakkalathil]

Looking for a SQL query to list all the Jira projects with its users and roles.

[Thomas B · Accepted solution]

@Reneesh Kottakkalathil - try this

SELECT project.Id, project.pname, project.pkey, projrole.PROJECTROLEID, projrole.ROLETYPEPARAMETER
FROM [jiradb].[dbo].[project] as project
JOIN [jiradb].[dbo].[projectroleactor] as projrole
ON project.Id = projrole.PID
Order by pname ASC

If you want to pull in project leads just add project.Lead

[Reneesh Kottakkalathil]

Excellent! Thanks so much @Thomas B 

[Thomas B]

Awesome @Reneesh Kottakkalathil glad it worked. Please accept the answer if you can. 

[Reneesh Kottakkalathil]

@Thomas B  Answer Accepted. Thanks.