Home > Microsoft SQL Server, Powershell > [Powershell] Set user permission in SSRS Item (SQL Reporting Server) using powershell

[Powershell] Set user permission in SSRS Item (SQL Reporting Server) using powershell


Combining both (article1 and article2), i was able to come up with a powershell script to set user permission in SQL Reporting Services (SSRS)

<#
.SYNOPSIS
	Set user permissions in SQL Reporting Services using Web Service

.DESCRIPTION
	Set user permissions in SQL Reporting Services using Web Service

.EXAMPLE
	Add-SSRSItemSecurity -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -groupUserName RPAULO\User1 -role Browser

.EXAMPLE
	Add-SSRSItemSecurity -url "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -u RPAULO\User1 -r "Content Manager"

#>
function Add-SSRSItemSecurity
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[Parameter(Position=1,Mandatory=$true)]
	[Alias("path")]
	[string]$itemPath,
	
	[Parameter(Position=2,Mandatory=$true)]
	[Alias("u")]
	[string]$groupUserName,
	
	[Parameter(Position=3,Mandatory=$true)]
	[Alias("r")]
	[string]$role,
	
	[Parameter(Position=2)]
	[bool]$inherit=$true
)

{
	
	#Fix item path if not starting with /
	if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}
	
	#Create Proxy
	Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential
	
	$type = $ssrsProxy.GetType().Namespace;
	$policyType = "{0}.Policy" -f $type;
	$roleType = "{0}.Role" -f $type;
	
	Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
	$policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);
	
	$a = 1;
	foreach($policy in $policies)
	{

		foreach($r in $policy.Roles)
		{
			$msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
			Write-Host $msg
		}
		$a+=1;
	}

	$msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
	Write-Host $msg
	
	$Policy = $policies | 
    Where-Object { $_.GroupUserName -eq $groupUserName } | 
    Select-Object -First 1
	
	if (-not $Policy) {
	    $Policy = New-Object ($policyType)
	    $Policy.GroupUserName = $GroupUserName
	    $Policy.Roles = @()
	    $Policies += $Policy
		$msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
		Write-Host $msg
	}

	$r = $Policy.Roles |
	    Where-Object { $_.Name -eq $role } |
	    Select-Object -First 1
	if (-not $r) {
	    $r = New-Object ($roleType)
	    $r.Name = $role
	    $Policy.Roles += $r
		$msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
		Write-Host $msg
	}
	
	#Set the policies
	$ssrsProxy.SetPolicies($itemPath,$policies);

}


Advertisements
  1. Aaron Smith
    June 16, 2015 at 3:20 pm

    I know this is an old post, but this just saved me a lot of time every day! Thank you!

  2. January 7, 2016 at 4:13 am

    Randy: Thanks very much. FYI, with some minor modifications, a similar script can be developed to add system roles (System Administrator and System User).

    • Mike
      January 6, 2017 at 9:34 pm

      Is it possible to have your version? (With System Administrator and System User)

  3. August 4, 2016 at 8:35 pm

    This is great to add permissions. What do I need to do to remove a policy/role? Do I still use SetPolicies method and what do I need to pass. An example would be great.

  4. October 5, 2016 at 11:34 am

    Hi Randy, this looks like something which can help me. I am relatively new to this as we have just started supporting a legacy server with 2008 R2 Reporting Services.
    I want to change the permissions for all the users on a particular folder from ‘Report Viewer’ to ‘Browser’. Is there a way I can achieve this?

  5. Vinnie
    October 14, 2016 at 8:59 pm

    Thank you very much ! Works like a charm. You should open source it on git so we can contribute some of our changes and create an SSRS module for PowerShell.

  1. February 22, 2012 at 10:41 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: