How to create a dashboard showing every dashboard's url the user have access to on report server?

[alexyin1053]

Currently we just started running PBIRS(2024.05) and keep getting requests from viewers hoping to see which dashboard they have acccess to.
The problem is that based on our current design, viewers only have access to specific dashboards' url, while each may be under different folders.

Currently we have an idea of using RLS + each dashboards' viewer information to create a dashboard to solve this problem.

Not sure if it's possible to get the information of each dashboards' name, url, assigned ppl's role(Browser, Content Manager, Publisher, ... etc.) and assigned ppl's windows account name from Report Server's Database.

Any help is appreciated.

Feel Free to let me know if any information is needed.

Thanks!

Best Regards,

Alex

[aduguid · Accepted solution]

Here's some SQL to get you started. I also have a bunch of reports I use to report from the system tables in GitHub.

DECLARE @ReportServerUrl AS NVARCHAR(200) = 'https://your_report_server.your_domain.com/';

WITH
report_types
AS
(
    SELECT tbl.* FROM (VALUES
	 ( 1, 'Folder')
    , ( 2, 'Report')
    --, ( 3, 'Resources')
    , ( 4, 'Linked Report')
    --, ( 5, 'Data Source')
    --, ( 6, 'Report Model')
    --, ( 7, 'Report Part')
    --, ( 8, 'Shared Dataset')
    --, ( 11, 'KPI')
    , ( 13, 'Power BI')
    --, ( 14, 'Excel')
    ) tbl ([Type], [TypeDescription]) 
)
SELECT 
	  [Item_Name] = cat.[Name]
	, [Folder_Path] = cat.[Path]
	, [Role_Name] = rol.[RoleName]
	, [User_Name_Format] = 
		LOWER
			(
			CASE 
				WHEN CHARINDEX('\', usr.[UserName]) > 0 THEN UPPER(SUBSTRING(usr.[UserName] ,CHARINDEX('\', usr.[UserName]) + 1, LEN(usr.[UserName])))
				ELSE usr.[UserName]
			END 
			)
	, [User_Name] = usr.[UserName]
	, [Folder_Security_Url] = @ReportServerUrl + 'Reports/manage/catalogitem/security' + cat.[Path]
FROM 
	[dbo].[Catalog] AS cat WITH(NOLOCK)
	INNER JOIN report_types AS typ WITH(NOLOCK) ON typ.[Type] = cat.[Type]
	LEFT JOIN [dbo].[PolicyUserRole] AS urol WITH(NOLOCK) ON urol.[PolicyID] = cat.[PolicyID]
	LEFT JOIN [dbo].[Roles] AS rol WITH(NOLOCK) ON urol.[RoleID] = rol.[RoleID]
	LEFT JOIN [dbo].[Policies] AS pol WITH(NOLOCK) ON urol.[PolicyID] = pol.[PolicyID]
	LEFT JOIN [dbo].[Users] AS usr WITH(NOLOCK) ON urol.[UserID] = usr.[UserID]
WHERE 
	1=1
	AND cat.[Path] != ''
     AND usr.[UserName] NOT IN('BUILTIN\Administrators');