Our Bitbucket backend DB is SQL Server, we have an out of date SSRS report that was intended to report users permissions by project and repo however it is out of date and not reporting correctly. The original author is long gone and unavailable. Of course the TSQL code is poorly commented. So I am hoping someone in the community can provide some guidance.
After reviewing the query in the SSRS Dataset definition the problem appears to come down to the CASE statement block below, it relates the permission id to a permissions group but we are unable to find a table that provides the permissions group id instead of the hard-coded permissions group names like 'PROJECT_ADMIN'. Does anyone have an idea where those values come from? Any help will be appreciated.
CASE
WHEN pp.perm_id = 0 then 'REPO_READ'
WHEN pp.perm_id = 1 then 'REPO_WRITE'
WHEN pp.perm_id = 2 then 'PROJECT_READ'
WHEN pp.perm_id = 3 then 'PROJECT_WRITE'
WHEN pp.perm_id = 4 then 'PROJECT_ADMIN'
WHEN pp.perm_id = 5 then 'PROJECT_CREATE'
WHEN pp.perm_id = 6 then 'ADMIN'
WHEN pp.perm_id = 7 then 'SYS_ADMIN'
WHEN pp.perm_id = 8 then 'REPO_ADMIN'
WHEN pp.perm_id = 9 then 'LICENSED_USER'
WHEN pp.perm_id = 10 then 'PROJECT_VIEW'
ELSE ' '
END as permission