To see who has folder/report level permissions in SSRS and Power BI Server, you have to join a couple of tables together – I started with a query I found at StackOverflow and built onto it – here’s my query (it pulls a bit more detail and shows not just permissions granted, but those not granted):
select case E.Path
when '' then '/'
else E.Path
end as ItemPathAndName,
concat(LEFT(E.Path, case
when len(E.Path)-len(E.Name) = 0 then 0
else len(E.Path)-len(E.Name)-1 end),'/'
) as ItemPath,
E.Name as ItemName,
case E.Type
when 1 then 'Folder'
when 13 then 'PBI Report'
else concat('Unknown (',Type,')')
end as ItemType,
case e.PolicyRoot
when 0 then 'Inherited'
when 1 then 'Customized'
else 'Unknown'
end as PermissionSource,
C.UserName,
D.RoleName,
--D.Description, -- Uncomment for role description
convert(bit, case when
a.id is null then 0
else 1
end) as ValidPermission
from dbo.Catalog E
cross join dbo.Users C
cross join dbo.Roles D
left
join dbo.PolicyUserRole A
on E.PolicyID = A.PolicyID
and A.RoleID = D.RoleID
and A.UserID = C.UserID
order by Path, C.UserName
Rather than just selecting the permissions, this query actually shows all possible permissions, and shows where users have or don’t have them. In my case, I wanted to see a list of users on the server that lacked access to specific reports and the “show me the permissions” query wouldn’t do that – additionally, this query can show you all the places that your permissions are manually set, rather than inherited.
To interpret them, I copied the results to Excel, created a pivot table, and then filtered using PermissionSource=Customized (to see all the custom permissions) or ValidPermission=0 (to see the places where people didn’t have permission to view an item).
The crossjoins can make the resultset a bit large, but it worked for me. If you want to filter things down a bit, you can add a WHERE clause near the end to folder those columns for the specific situation you’re looking for.
