Viewing active (and missing) permissions in SSRS and Power BI Server

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.