Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
4 | |
4 | |
3 | |
3 |
User | Count |
---|---|
14 | |
9 | |
6 | |
3 | |
3 |