Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How do you list the users and permissions for all projects?

This article is quite handy, but the query it provides will return only repositories that have their own unique assigned users (permissions). But we have a lot of repos that inherit permissions from the project they are in, and those repos won't show in the query from the article. 

Does anyone have suggestions for a SQL query that will return all users of all projects, by project?

1 answer

The following SQL query provides a list of projects and repositories with their respective users alongside user permission in Bitbucket. This query has been tested with Microsoft SQL Server.

WITH project_users AS (
SELECT DISTINCT p.id                                       AS "Project ID",
                p.name                                     AS "Project Name",
                r.name                                     AS "Repo Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   AS "Fullname (Individual)",
                pp.perm_id                                 AS "Permission Type"
FROM   dbo.project p
       JOIN dbo.sta_normal_project np
        ON np.project_id = p.id
       LEFT JOIN dbo.sta_project_permission pp
              ON pp.project_id = p.id
       LEFT JOIN dbo.repository r
              ON r.project_id = p.id
    LEFT JOIN dbo.sta_repo_permission srp
              ON srp.repo_id = r.id
       LEFT JOIN dbo.sta_normal_user u
              ON srp.user_id = u.user_id
       LEFT JOIN dbo.cwd_user cu
              ON cu.lower_user_name = u.name
 
WHERE  srp.group_name IS NOT NULL  OR srp.user_id IS NOT NULL
 
UNION
SELECT DISTINCT p.id                                       AS "Project ID",
                p.name                                     AS "Project Name",
                ''                                         AS "Repo Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   AS "Fullname (Individual)",
                pp.perm_id                                 AS "Permission Type"
FROM   dbo.project p
       JOIN dbo.sta_normal_project np
         ON np.project_id = p.id
       LEFT JOIN dbo.sta_project_permission pp
              ON pp.project_id = p.id
       LEFT JOIN dbo.sta_normal_user u
              ON pp.user_id = u.user_id
       LEFT JOIN dbo.cwd_user cu
              ON cu.lower_user_name = u.name
WHERE  pp.group_name IS NOT NULL  OR pp.user_id IS NOT NULL
)
 
SELECT * FROM project_users
ORDER  BY [Project ID] ;

 Permission type:

PROJECT_VIEW=10
REPO_READ=0
REPO_WRITE=1
REPO_ADMIN=8
PROJECT_READ=2
PROJECT_WRITE=3
PROJECT_ADMIN=4
LICENSED_USER=9
PROJECT_CREATE=5
ADMIN=6
SYS_ADMIN=7

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
6.6.3
TAGS
Community showcase
Published in Bitbucket

New improvements to user management in Bitbucket Cloud 👥

Hey Community! We’re willing to wager that quite a few of you not only use Bitbucket, but administer it too. Our team is excited to share that we’ll be releasing improvements throughout this month of...

3,930 views 10 16
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you