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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
alexyin1053
Helper I
Helper I

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
Super User
Super User

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
Super User
Super User

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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