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

How to decrypt stored password from SSMS registered servers

Cause for Parameter is incorrect 0x80070057 error in ssis

How to fail sql server agent job if powershell script fails