Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alexyin1053
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION
aduguid
Solution Sage
Solution Sage

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');

 

View solution in original post

1 REPLY 1
aduguid
Solution Sage
Solution Sage

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');

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.