Forum Discussion
Power BI Report Server security audit report permissions
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
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.RoleNameFROM
[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_tableRegards,
Marcin
1 Reply
Marcin_PLHelper I
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.RoleNameFROM
[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_tableRegards,
Marcin