Migrate SSRS Subscription Owners with PowerShell

Premier Developer

Developer

App Dev Manager Reed Robison shares an example of how to automate the assignment of SSRS subscription owners.


If you are migrating SSRS content to a new server, this is fairly easy to do and documented in a number of articles like Sample Reporting Services rs.exe Script to Copy Content between Report Servers.  You may find that after you move the content that subscription owners are automatically set to the account used to perform the migration.  There is an API you can use to assign a new owner and scripts to list subscriptions owners, but if you have to do this for a large number of servers, it can be a lot of work.  With that in mind, I wrote a script to help automate the assignment of subscriptions owners based on the source content.

This script assumes you have migrated the Reporting Services content with RS.EXE similar to the process documented here.  RS.EXE copies the content following the limitations outlined in the article.  There should be subscriptions that were migrated but they will have inherited the permissions of the account used to do the migration, which you want to change.

The script is based on the samples provided in Manage Subscription Owners and Run Subscription – PowerShell.  Those samples include generic scripts to list owners, assign owners, etc.  They use the ChangeSubscriptionOwner API.

Description

The script (MoveOwner_All_SSRS_Subscriptions) takes a source server and a destination server as well as credentials used to manage each.  It enumerates subscriptions on the source server and attempts to find a matching subscription on the destinations server.  Since the new subscription will have a different GUID after migration, it attempts to do this by comparing path, server, and description.  Assuming you migrated the content with rs.exe, those properties should have been preserved.  If it finds a match, it looks at the original owner and attempts to assign that owner to the destination subscriptions using the new GUID for the subscription.

Limitations & Considerations

  • This script assumes we’re migrating native modes only, not SharePoint mode.  You could easily modify the script to handle the SharePoint scenario as well.
  • The script is configured for HTTP.  If you enforce HTTPS, you will need to modify the proxy reference to use https instead of http.
  • When the ChangeSubscriptionOwner API attempts to assign the owner, it will need to successfully validate the account – so the account used to connect to the destination server will need proper permission to do this.

Syntax:

Powershell MoveOwner_All_SSRS_Subscriptions.ps1 “[server]/reportserver” “[source domain]\[user]” “[source user password]” “[dest server]/reportserver“ “[dest domain]\[user]” “dest user password” *This syntax is similar to what you probably used with rs.exe

 

Example (where the source server is server1 and the destination server is server2 and both systems are configured with a user authorized as “Demo” and password set to “Password101”:

Powershell c:\rs_scripts\MoveOwner_All_SSRS_Subscriptions.ps1 “server1/reportserver” “server1\Demo” “Password101” “server2/reportserver” “server2\Demo” “Password101”

 

MoveOwner_All_SSRS_Subscriptions.ps1

Param(  
    [string]$sourceserver,  
    [string]$sourceuser, 
    [string]$sourcepwd, 
    [string]$destserver,  
    [string]$destuser,
    [string]$destpwd
   )  

$errors = 0
$assigned = 0
$subcount = 0

# Setup credentials to connect with the source server
$Credential = [System.Management.Automation.PSCredential]::new($sourceuser,(ConvertTo-SecureString $sourcepwd -AsPlainText -Force))  

# Retrieve the list of subscriptions on the source server
$rs2010 = New-WebServiceProxy -Uri "http://$sourceserver/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  
$sourcesubscriptions = $rs2010.ListSubscriptions("/"); # use "/" for default native mode site  

# Setup the crentials to connect with the destination server  
$Credential = [System.Management.Automation.PSCredential]::new($destuser,(ConvertTo-SecureString $destpwd -AsPlainText -Force))  

# Retrieve the list of subscriptions on the destination server
$rs2010 = New-WebServiceProxy -Uri "http://$destserver/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  
$destsubscriptions = $rs2010.ListSubscriptions("/"); # use "/" for default native mode site  

# Enumerate a list of subscriptions on source and destination servers
#Write-Host " "  
#Write-Host "----- Source: $server's Subscriptions: "  
#$sourcesubscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status
#
#Write-Host "----- Destination: $server's Subscriptions: "  
#$destsubscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status

# Display a list of subscriptions matched by report, path, and description
foreach ($source in $sourcesubscriptions)
{
    $subcount++
    for ($i = 0; $i -lt $destsubscriptions.count; $i++)
    {
         Try
         {
             if ( $source.Path -eq $destsubscriptions[$i].Path -And $source.report -eq $destsubscriptions[$i].report -And $source.Description -eq $destsubscriptions[$i].Description ) 
             {
                Write-Host "Source Subscription : ", $source.Path, $Source.report, $Source.Owner, $Source.SubscriptionID
                Write-Host "Dest Subscription   :", $destsubscriptions[$i].Path, $destsubscriptions[$i].report, $destsubscriptions[$i].Owner, $destsubscriptions[$i].SubscriptionID
                
                $newowner = $source.Owner
                if (-not $newowner.Contains("\"))
                {
                   # If the source owner does not have a domain attached for any reason, assign a default domain here (replace defaultdomain with your domain)
                   $newowner = "defaultdomain\" + $newowner
                }

                Write-Host "New Owner will be assigned to : ", $newowner

                #Setup new proxy to reduce change of timeout holding the previous one open too long (large migrations)
                $rs2010 = New-WebServiceProxy -Uri "http://$destserver/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  
                $rs2010.Timeout = 200000
                Write-Host "Setting Owner" (get-date).ToString('T')
                $rs2010.ChangeSubscriptionOwner($destsubscriptions[$i].SubscriptionID, $newowner)

                # Check to see if the source subscription is disabled and disable the destination
                if ($source.Status -eq "Disabled" )
                {
                   $rs2010.DisableSubscription($destsubscriptions[$i].SubscriptionID)
                   Write-Host "Subscription Disabled"
                }
                
                Write-Host "Owner Assignment Complete" (get-date).ToString('T')
                $assigned++
             }
         }
         Catch
         {
             Write-Host "Exception Occured at " (get-date).ToString('T')
             Write-Host $_.Exception.Message
             $errors++
         }
    }
    Write-Host " "
}
if ($errors -gt 0 )
{
    Write-Host "*** ", $errors, "owners could not be assigned.  Either these accounts could not be validated or you do not have pemissions to make the owner assignments."
}
Write-Host $assigned, "owners assigned on", $subcount, "subscriptions."

 

0 comments

Leave a comment