Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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');
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');
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.