Set site and folder security using powershell

Setting up SSRS security is a two step process. Site security, then Folder security.
This can be handled using the following scripts. Sources from https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/
https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CSecurity%5CGrant-AccessToRS.ps1

Add-SsrsItemSecurity.ps1


<#
.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 -url "http://[ServerName]/ReportServer/ReportService2010.asmx?WSDL" -itemPath "MyReportFolder" -u domain\user -r "Content Manager" -inherit $true
#>
param 
(
 [Parameter(Mandatory=$true)][Alias("url")][string]$webServiceUrl,
 [Parameter(Mandatory=$true)][Alias("path")][string]$itemPath,
 [Parameter(Mandatory=$true)][Alias("u")][string]$groupUserName,
 [Parameter(Mandatory=$true)][Alias("r")][string]$role,
 [bool]$inherit=$true
)
 
    if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}
     
    Write-Host "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 "Retrieving all existing policies."
    $policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);
     
    $a = 1;
    foreach($policy in $policies)
    {
 
        foreach($r in $policy.Roles)
        {
            $msg = "  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }
 
    $msg = "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 = "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 = "Adding new role: '{0}'" -f $role
        Write-Host $msg
    }
     
    $ssrsProxy.SetPolicies($itemPath,$policies);
 

Add-SsrsSiteSettingSecurity.ps1


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

.EXAMPLE
    Add-SsrsSiteSettingSecurity -url "http://[ServerName]/ReportServer/ReportService2010.asmx?WSDL" -u domain\user -r "System User"
 
#>
param 
(
 [Parameter(Mandatory=$true)][Alias("url")][string]$webServiceUrl,
 [Parameter(Mandatory=$true)][Alias("u")][string]$groupUserName,
 [Parameter(Mandatory=$true)][Alias("r")][string]$role
)
 
    Write-Host "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 "Retrieving all existing system policies."
    $policies = $ssrsProxy.GetSystemPolicies();
     
    $a = 1;
    foreach($policy in $policies)
    {
 
        foreach($r in $policy.Roles)
        {
            $msg = "  Existing System Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }
 
    $msg = "Total Existing System 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 = "Adding new system 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 = "Adding new system role: '{0}'" -f $role
        Write-Host $msg
    }
     
    $ssrsProxy.SetSystemPolicies($policies);
 

Comments

Popular posts from this blog

SSIS: Set parent package variable from child package

How to decrypt stored password from SSMS registered servers

Constructing nested array structs in BigQuery