{"id":39374,"date":"2020-05-24T12:23:23","date_gmt":"2020-05-24T19:23:23","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/premier-developer\/?p=39374"},"modified":"2020-05-26T07:54:03","modified_gmt":"2020-05-26T14:54:03","slug":"migrate-ssrs-subscription-owners-with-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/migrate-ssrs-subscription-owners-with-powershell\/","title":{"rendered":"Migrate SSRS Subscription Owners with PowerShell"},"content":{"rendered":"<p>App Dev Manager <a href=\"https:\/\/www.linkedin.com\/in\/reedrobison\/\">Reed Robison<\/a> shares an example of how to automate the assignment of SSRS subscription owners.<\/p>\n<hr \/>\n<p>If you are migrating SSRS content to a new server, this is fairly easy to do and documented in a number of articles like <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/reporting-services\/tools\/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers?view=sql-server-ver15\">Sample Reporting Services rs.exe Script to Copy Content between Report Servers<\/a>.\u00a0 You may find that after you move the content that subscription owners are automatically set to the account used to perform the migration.\u00a0 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.\u00a0 With that in mind, I wrote a script to help automate the assignment of subscriptions owners based on the source content.<\/p>\n<p>This script assumes you have migrated the Reporting Services content with RS.EXE similar to the process documented <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/reporting-services\/tools\/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers?view=sql-server-ver15\">here<\/a>.\u00a0 RS.EXE copies the content following the limitations outlined in the article.\u00a0 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.<\/p>\n<p>The script is based on the samples provided in <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/reporting-services\/subscriptions\/manage-subscription-owners-and-run-subscription-powershell?view=sql-server-ver15\">Manage Subscription Owners and Run Subscription &#8211; PowerShell<\/a>.\u00a0 Those samples include generic scripts to list owners, assign owners, etc.\u00a0 They use the <a href=\"https:\/\/msdn.microsoft.com\/library\/reportservice2010.reportingservice2010.changesubscriptionowner(v=SQL.130).aspx\">ChangeSubscriptionOwner<\/a> API.<\/p>\n<p><strong>Description<\/strong><\/p>\n<p>The script (MoveOwner_All_SSRS_Subscriptions) takes a source server and a destination server as well as credentials used to manage each.\u00a0 It enumerates subscriptions on the source server and attempts to find a matching subscription on the destinations server.\u00a0 Since the new subscription will have a different GUID after migration, it attempts to do this by comparing path, server, and description.\u00a0 Assuming you migrated the content with rs.exe, those properties should have been preserved.\u00a0 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.<\/p>\n<p><strong>Limitations &amp; Considerations<\/strong><\/p>\n<ul>\n<li>This script assumes we\u2019re migrating native modes only, not SharePoint mode.\u00a0 You could easily modify the script to handle the SharePoint scenario as well.<\/li>\n<li>The script is configured for HTTP.\u00a0 If you enforce HTTPS, you will need to modify the proxy reference to use <em>https<\/em> instead of <em>http<\/em>.<\/li>\n<li>When the ChangeSubscriptionOwner API attempts to assign the owner, it will need to successfully validate the account \u2013 so the account used to connect to the destination server will need proper permission to do this.<\/li>\n<\/ul>\n<p><strong>Syntax<\/strong>:<\/p>\n<p>Powershell MoveOwner_All_SSRS_Subscriptions.ps1 \u201c[server]\/reportserver\u201d \u201c[source domain]\\[user]\u201d \u201c[source user password]\u201d \u201c[dest server]\/reportserver\u201c \u201c[dest domain]\\[user]\u201d \u201cdest user password\u201d\n<em>*This syntax is similar to what you probably used with rs.exe<\/em><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Example<\/strong> (where the source server is server1 and the destination server is server2 and both systems are configured with a user authorized as \u201cDemo\u201d and password set to \u201cPassword101\u201d:<\/p>\n<p>Powershell c:\\rs_scripts\\MoveOwner_All_SSRS_Subscriptions.ps1 &#8220;server1\/reportserver&#8221; &#8220;server1\\Demo&#8221; &#8220;Password101&#8221; &#8220;server2\/reportserver&#8221; &#8220;server2\\Demo&#8221; &#8220;Password101&#8221;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>MoveOwner_All_SSRS_Subscriptions.ps1<\/strong><\/p>\n<pre class=\"prettyprint\">Param(  \r\n    [string]$sourceserver,  \r\n    [string]$sourceuser, \r\n    [string]$sourcepwd, \r\n    [string]$destserver,  \r\n    [string]$destuser,\r\n    [string]$destpwd\r\n   )  \r\n\r\n$errors = 0\r\n$assigned = 0\r\n$subcount = 0\r\n\r\n# Setup credentials to connect with the source server\r\n$Credential = [System.Management.Automation.PSCredential]::new($sourceuser,(ConvertTo-SecureString $sourcepwd -AsPlainText -Force))  \r\n\r\n# Retrieve the list of subscriptions on the source server\r\n$rs2010 = New-WebServiceProxy -Uri \"http:\/\/$sourceserver\/ReportService2010.asmx\" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  \r\n$sourcesubscriptions = $rs2010.ListSubscriptions(\"\/\"); # use \"\/\" for default native mode site  \r\n\r\n# Setup the crentials to connect with the destination server  \r\n$Credential = [System.Management.Automation.PSCredential]::new($destuser,(ConvertTo-SecureString $destpwd -AsPlainText -Force))  \r\n\r\n# Retrieve the list of subscriptions on the destination server\r\n$rs2010 = New-WebServiceProxy -Uri \"http:\/\/$destserver\/ReportService2010.asmx\" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  \r\n$destsubscriptions = $rs2010.ListSubscriptions(\"\/\"); # use \"\/\" for default native mode site  \r\n\r\n# Enumerate a list of subscriptions on source and destination servers\r\n#Write-Host \" \"  \r\n#Write-Host \"----- Source: $server's Subscriptions: \"  \r\n#$sourcesubscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status\r\n#\r\n#Write-Host \"----- Destination: $server's Subscriptions: \"  \r\n#$destsubscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status\r\n\r\n# Display a list of subscriptions matched by report, path, and description\r\nforeach ($source in $sourcesubscriptions)\r\n{\r\n    $subcount++\r\n    for ($i = 0; $i -lt $destsubscriptions.count; $i++)\r\n    {\r\n         Try\r\n         {\r\n             if ( $source.Path -eq $destsubscriptions[$i].Path -And $source.report -eq $destsubscriptions[$i].report -And $source.Description -eq $destsubscriptions[$i].Description ) \r\n             {\r\n                Write-Host \"Source Subscription : \", $source.Path, $Source.report, $Source.Owner, $Source.SubscriptionID\r\n                Write-Host \"Dest Subscription   :\", $destsubscriptions[$i].Path, $destsubscriptions[$i].report, $destsubscriptions[$i].Owner, $destsubscriptions[$i].SubscriptionID\r\n                \r\n                $newowner = $source.Owner\r\n                if (-not $newowner.Contains(\"\\\"))\r\n                {\r\n                   # If the source owner does not have a domain attached for any reason, assign a default domain here (replace defaultdomain with your domain)\r\n                   $newowner = \"defaultdomain\\\" + $newowner\r\n                }\r\n\r\n                Write-Host \"New Owner will be assigned to : \", $newowner\r\n\r\n                #Setup new proxy to reduce change of timeout holding the previous one open too long (large migrations)\r\n                $rs2010 = New-WebServiceProxy -Uri \"http:\/\/$destserver\/ReportService2010.asmx\" -Namespace SSRS.ReportingService2010 -Credential $Credential ;  \r\n                $rs2010.Timeout = 200000\r\n                Write-Host \"Setting Owner\" (get-date).ToString('T')\r\n                $rs2010.ChangeSubscriptionOwner($destsubscriptions[$i].SubscriptionID, $newowner)\r\n\r\n                # Check to see if the source subscription is disabled and disable the destination\r\n                if ($source.Status -eq \"Disabled\" )\r\n                {\r\n                   $rs2010.DisableSubscription($destsubscriptions[$i].SubscriptionID)\r\n                   Write-Host \"Subscription Disabled\"\r\n                }\r\n                \r\n                Write-Host \"Owner Assignment Complete\" (get-date).ToString('T')\r\n                $assigned++\r\n             }\r\n         }\r\n         Catch\r\n         {\r\n             Write-Host \"Exception Occured at \" (get-date).ToString('T')\r\n             Write-Host $_.Exception.Message\r\n             $errors++\r\n         }\r\n    }\r\n    Write-Host \" \"\r\n}\r\nif ($errors -gt 0 )\r\n{\r\n    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.\"\r\n}\r\nWrite-Host $assigned, \"owners assigned on\", $subcount, \"subscriptions.\"\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You may find that after you move the content that subscription owners are automatically set to the account used to perform the migration.\u00a0 There is an API you can use to assign a new owner and scripts to list subscriptions owners, but I if you have to do this for a large number of servers, it can be a lot of work.<\/p>\n","protected":false},"author":582,"featured_media":38586,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[5940],"tags":[102,351,3],"class_list":["post-39374","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development","tag-powershell","tag-ssrs","tag-team"],"acf":[],"blog_post_summary":"<p>You may find that after you move the content that subscription owners are automatically set to the account used to perform the migration.\u00a0 There is an API you can use to assign a new owner and scripts to list subscriptions owners, but I if you have to do this for a large number of servers, it can be a lot of work.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/39374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=39374"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/39374\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/38586"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=39374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=39374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=39374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}