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.