SQL to check Bitbucket user permission

Bitbucket permission can be assigned at project level or repository level for a group and user.

The SQL (MSSQL Server) below can be used to check all projects & repositories permission for a given user, include the access assigned to an individual user or a group.

with repo_access 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)",
                srp.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)"
from   dbo.project p
       join dbo.sta_normal_project np
         on np.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
       left join dbo.cwd_membership cm
              on cm.lower_parent_name = srp.group_name
       left join dbo.cwd_user cu1
              on cu1.lower_user_name = cm.lower_child_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.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)"
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
       left join dbo.cwd_membership cm
              on cm.lower_parent_name = pp.group_name
       left join dbo.cwd_user cu1
              on cu1.lower_user_name = cm.lower_child_name
where  pp.group_name is not null  or pp.user_id is not null
)

select * from repo_access where [Username (Individual)] = 'Replace with UserId' or [Username (Group)] = 'Replace with UserID'
order  by [Project ID] ;