topic Re: Exporting list of those with access to the Report in Report Server

Exporting list of those with access to the Report

Anonymous — Mon, 04 Mar 2019 20:16:32 GMT

Hi,

I'd like to export the list of those that my report has been shared with. The list has gotten rather long and exporting is the most efficient way. Is there a way to do this? I am already at the list of recipients, I just don't see a way to export.

Thanks

Re: Exporting list of those with access to the Report

Anonymous — Mon, 11 Mar 2019 13:10:42 GMT

You can proably use the REST API to get this using some PowerShell.

You can of course query the backend database (ReportServer DB) if you have access with something like this

All our security is applied at a folder level (hence the Type=1) if its an indivuidual report you would need to figure out how to query for that within the dbo.catalog table.

SELECT	u.UserName
	   ,r.RoleName
FROM	dbo.PolicyUserRole pur
		LEFT OUTER JOIN dbo.Roles r
			ON r.RoleID = pur.RoleID
		LEFT OUTER JOIN dbo.Users u
			ON u.UserID = pur.UserID
WHERE	pur.PolicyID = (
						 SELECT	PolicyID
						 FROM	dbo.Catalog
						 WHERE	Type = 1
								AND Path = '<PATH_TO_FOLDER>' );

Re: Exporting list of those with access to the Report

Anonymous — Mon, 11 Mar 2019 13:31:13 GMT

If you install the ReportingServicesTools PowerShell module from here

https://github.com/Microsoft/ReportingServicesTools

you can get the data back pretty easily.

Import-Module ReportingServicesTools

$CatItemRoles = Get-RsCatalogItemRole -Path "/ThisFolderOrItem" -ReportServerUri "http://yourServerName/ReportServerPBI"

$CatItemRoles | Export-Csv -LiteralPath "C:\CatItemRoles.csv"

Re: Exporting list of those with access to the Report

Prabhav_Jain — Thu, 31 Oct 2019 08:10:42 GMT

from where i can get the access of the db

Re: Exporting list of those with access to the Report

Anonymous — Mon, 11 Nov 2019 10:50:42 GMT

When you install PBI Reporting Server it has to be setup with a database backend. You can see where this is by running the PBI Reporting Server config tool on your PBI Reporting server. You will need permissions to go query that server and DB.

Re: Exporting list of those with access to the Report

AlexanderK79 — Mon, 10 May 2021 08:15:32 GMT

Open the Manage permissions page of the dataset in Chrome, and then start a developer mode using F12

in the console area type the following command:

$('span.emailColumn').each(function() { console.log ($(this).text())})

This will give you all the mail addresses 🙂 in a list

or.... even cooler:
$('.permissionTable li').each(function() { console.log ( $(this).children('span.nameColumn').text(),",",$(this).children('span.emailColumn').text(),",",$(this).children('span.memberAccessColumn').text())})

Check Bart's updated version of the script since the release of the new sharing experience: https://community.powerbi.com/t5/Report-Server/Exporting-list-of-those-with-access-to-the-Report/m-p/1826969/highlight/true#M19196

which will give you a CSV list in the console to be copy/pasted

Re: Exporting list of those with access to the Report

Anonymous — Tue, 12 May 2020 09:12:54 GMT

Hi,

Where do you get from the Report Server URI parameter?

Thanks!

Cristina

Re: Exporting list of those with access to the Report

AlexanderK79 — Tue, 12 May 2020 10:12:09 GMT

You don't need the Report Server when you use the trick with the debugging tools

Re: Exporting list of those with access to the Report

Anonymous — Wed, 13 May 2020 07:50:34 GMT

No? What do you need then? I write this on Power Shell: Import-Module ReportingServicesTools; Get-RsCatalogItemRole this is what I'm getting:

Failed to establish proxy connection to http://localhost/reportserver/ : Failed to establish proxy connection to http://localhost/reportserver/ReportService2010.asmx : There was an error
downloading 'http://localhost/reportserver/ReportService2010.asmx'

The first lines of code are part of this:

Import-Module ReportingServicesTools
$CatItemRoles = Get-RsCatalogItemRole -Path "/ThisFolderOrItem" -ReportServerUri "http://yourServerName/ReportServerPBI"
$CatItemRoles | Export-Csv -LiteralPath "C:\CatItemRoles.csv"

That's why I was asking for the ReportServerUri parameter.

Please, help me 🙂

Kind regards,

Cristina Simion

Re: Exporting list of those with access to the Report

AlexanderK79 — Wed, 13 May 2020 08:11:35 GMT

Sorry, but I don't know... I took a different approach

Re: Exporting list of those with access to the Report

Anonymous — Wed, 13 May 2020 10:07:06 GMT

Could you explain me which aproach did you take?

Thank you so much!

Re: Exporting list of those with access to the Report

tamilkumaran — Wed, 10 Jun 2020 06:16:07 GMT

@Anonymous He took the console approach which he did explain in his previous message. It works perfectly!!

Re: Exporting list of those with access to the Report

Anonymous — Fri, 11 Sep 2020 02:43:48 GMT

Thank you @AlexanderK79 , worked perfectly

Re: Exporting list of those with access to the Report

marteaga — Mon, 19 Oct 2020 17:10:48 GMT

This is great!!! thank you! do you have a similar coding for extracting the users assigned to each row-level security role? this would be so helpful to control accesses and manage the security roles. Thank you in advance!

Re: Exporting list of those with access to the Report

AkshayManke — Tue, 01 Dec 2020 13:00:22 GMT

Hi, i tried the solution suggested by you and it worked very perfectly. I just would want to tweek some parameters like instead of Email i want Alias. How should i do that? From where i can get more references to make more combinations?

Many Thanks,

Re: Exporting list of those with access to the Report

AlexanderK79 — Tue, 01 Dec 2020 18:09:04 GMT

no, I'm sorry... you can only retrieve what shows up in the permissions list...

have you tried:

$('.permissionTable li').each(function() { console.log ( $(this).children('span.nameColumn').text(),",",$(this).children('span.emailColumn').text(),",",$(this).children('span.memberAccessColumn').text())})

Re: Exporting list of those with access to the Report

Anonymous — Wed, 10 Feb 2021 15:29:04 GMT

Hi, Can it be that something changed on the site? It is not working for me.

Thanks in advance!

Re: Exporting list of those with access to the Report

AlexanderK79 — Thu, 11 Feb 2021 07:07:58 GMT

Hi there,

just tried it and it still works for me.... did you open the Manage permissions page of the dataset first? The URL that starts with https://app.powerbi.com/groups/me/permission/report

Re: Exporting list of those with access to the Report

Anonymous — Thu, 11 Feb 2021 08:27:23 GMT

Yes it worked, I was looking at the pop-up screen. Great thanks!

Re: Exporting list of those with access to the Report

Anonymous — Tue, 02 Mar 2021 03:57:05 GMT

@AlexanderK79 do you have a similar coding for extracting the users assigned to each row-level security role? this would be so helpful to control accesses and manage the security roles. Thank you in advance!