Power BI Report Server security audit report permissions

[mczeigler12]

We are in the process of a security audit and need to complile a list of who has access to each PBI report on the Power BI Report Server. Is there an area within PBI Report Server or SQL to retreive a report permissions table? Thank you 

[Marcin_PL · Accepted solution]

Hello,

try this query in your Report Server's SQL database:

SELECT *

FROM (
SELECT
P.PolicyID as Policy_ID
,U.UserName
,C.Name as ObjectName
,C.Type as TypeID
,case
when Type=1 then 'Folder'
when Type=13 then 'PBIReport'
when Type=3 then 'Resource'
when Type=14 then 'Excel'
when Type=5 then 'DataSource'
when Type=8 then 'DataSet'
when Type=2 then 'PaginatedReport'
when Type=12 then 'MobileReport'
when Type=11 then 'KPI'
else '' end as TypeName
,C.Path
,C.ItemID
,R.RoleName

FROM
[dbo].[Users] U
join [dbo].[PolicyUserRole] PUR on U.UserID=PUR.UserID
join [dbo].[Policies] P on P.PolicyID=PUR.PolicyID
join [dbo].[Roles] R on R.RoleID=PUR.RoleID
join [dbo].[Catalog] C on C.PolicyID = P.PolicyID

) BASE

PIVOT(
COUNT(RoleName)
FOR RoleName IN (
[Browser],
[Content Manager],
[Publisher],
[Report Builder],
[My Reports]
)
) AS pivot_table

Regards,

Marcin