Manage subscription owners and run subscription - PowerShell
Applies to: 
SQL Server 2016 (13.x) Reporting Services and later
SharePoint
Starting with SQL Server 2008 R2 (10.50.x) Reporting Services you can programmatically transfer the ownership of a Reporting Services subscription from one user to another. This article provides several Windows PowerShell scripts you can use to change or simply list subscription ownership. Each sample includes sample syntax for both Native mode and SharePoint mode. After you change the subscription owner, the subscription will then execute in the security context of the new owner, and the User!UserID field in the report displays the value of new owner. For more information on the object model the PowerShell samples call, see ChangeSubscriptionOwner.
In this article:
Script: Change ownership for all subscriptions owned by a specific user
Script: List all subscriptions associated with a specific report
How to use the scripts
Permissions
This section summarizes the permission levels required to use each of the methods for both Native and SharePoint mode Reporting Services. The scripts in this article use the following Reporting Services methods:
The method ReportingService2010.FireEvent is only used in the last script to trigger a specific subscription to run. If you don't plan to use that script, you can ignore the permission requirements for the FireEvent method.
Native mode:
List Subscriptions: ReportOperation enumeration on the report AND the user is the subscription owner) OR ReadAnySubscription.
Change Subscriptions: The user must be a member of the BUILTIN\Administrators group
List Children: ReadProperties on Item
Fire Event: GenerateEvents (System)
SharePoint mode:
List Subscriptions: ManageAlerts OR CreateAlerts on the report AND the user is the subscription owner and the subscription is a timed subscription).
Change Subscriptions: ManageWeb
List Children: ViewListItems
Fire Event: ManageWeb
For more information, see Compare roles and tasks in Reporting Services to SharePoint groups and permissions.
Script usage
Create Script files (.ps1)
Create a folder named c:\scripts. If you choose a different folder, then modify the folder name used in the example command-line syntax statements.
Create a text file for each script and save the files to the c:\scripts folder. When you create the
.ps1files, use the name from each example command-line syntax.Open a command prompt with administrative privileges.
Run each script file by using the sample command-line syntax provided with each example.
Tested environments
The scripts in this article were tested on PowerShell version 3 and with the following versions of Reporting Services:
-
SQL Server 2014 (12.x)
-
SQL Server 2012 (11.x)
-
SQL Server 2008 R2 (10.50.x)
Script: List the ownership of all subscriptions
This script lists all of the subscriptions on a site. You can use this script to test your connection or to verify the report path and subscription ID for use in the other scripts. This script is also useful to audit what subscriptions exist and who owns them.
Native mode syntax:
powershell c:\scripts\ListAll_SSRS_Subscriptions.ps1 "[server]/reportserver" "/"
SharePoint mode syntax:
powershell c:\scripts\ListAll_SSRS_Subscriptions.ps1 "[server]/_vti_bin/reportserver" "https://[server]"
Script:
# Parameters
# server - server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)
Param(
[string]$server,
[string]$site
)
$rs2010 += New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site); # use "/" for default native mode site
Write-Host " "
Write-Host "----- $server's Subscriptions: "
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status
Tip
To verify site URLS in SharePoint mode, use the SharePoint cmdlet Get-SPSite. For more information, see Get-SPSite.
Script: List all subscriptions owned by a specific user
This script lists all of the subscriptions owned by a specific user. You can use this script to test your connection or to verify the report path and subscription ID for use in the other scripts. This script is useful when someone in your organization leaves and you want to verify what subscriptions they owned. With the script, you can change the owner or delete the subscription.
Native mode syntax:
powershell c:\scripts\ListAll_SSRS_Subscriptions4User.ps1 "[Domain]\[user]" "[server]/reportserver" "/"
SharePoint mode syntax:
powershell c:\scripts\ListAll_SSRS_Subscriptions4User.ps1 "[Domain]\[user]" "[server]/_vti_bin/reportserver" "https://[server]"
Script:
# Parameters:
# currentOwner - DOMAIN\USER that owns the subscriptions you wish to change
# server - server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)
# site - use "/" for default native mode site
Param(
[string]$currentOwner,
[string]$server,
[string]$site
)
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site);
Write-Host " "
Write-Host " "
Write-Host "----- $currentOwner's Subscriptions: "
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted,Status | where {$_.owner -eq $currentOwner}
Script: Change ownership for all subscriptions owned by a specific user
This script changes the ownership for all subscriptions owned by a specific user to the new owner parameter.
Native mode syntax:
powershell c:\scripts\ChangeALL_SSRS_SubscriptionOwner.ps1 "[Domain]\current owner]" "[Domain]\[new owner]" "[server]/reportserver"
SharePoint mode syntax:
powershell c:\scripts\ChangeALL_SSRS_SubscriptionOwner.ps1 "[Domain]\{current owner]" "[Domain]\[new owner]" "[server]/_vti_bin/reportserver"
Script:
# Parameters:
# currentOwner - DOMAIN\USER that owns the subscriptions you wish to change
# newOwner - DOMAIN\USER that will own the subscriptions you wish to change
# server - server and instance name (e.g. myserver/reportserver, myserver/reportserver_db2, myserver/_vti_bin/reportserver)
Param(
[string]$currentOwner,
[string]$newOwner,
[string]$server
)
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$items = $rs2010.ListChildren("/", $true);
$subscriptions = @();
ForEach ($item in $items)
{
if ($item.TypeName -eq "Report")
{
$curRepSubs = $rs2010.ListSubscriptions($item.Path);
ForEach ($curRepSub in $curRepSubs)
{
if ($curRepSub.Owner -eq $currentOwner)
{
$subscriptions += $curRepSub;
}
}
}
}
Write-Host " "
Write-Host " "
Write-Host -foregroundcolor "green" "----- $currentOwner's Subscriptions changing ownership to $newOwner : "
$subscriptions | select SubscriptionID, Owner, Path, Description, Status | format-table -AutoSize
ForEach ($sub in $subscriptions)
{
$rs2010.ChangeSubscriptionOwner($sub.SubscriptionID, $newOwner);
}
$subs2 = @();
ForEach ($item in $items)
{
if ($item.TypeName -eq "Report")
{
$subs2 += $rs2010.ListSubscriptions($item.Path);
}
}
Script: List all subscriptions associated with a specific report
This script lists all of the subscriptions associated with a specific report. The report path syntax is different SharePoint mode, which requires a full URL. In the syntax examples, the report name used is "title only," which contains a space and therefore requires the single quotes around the report name.
Native mode syntax:
powershell c:\scripts\List_SSRS_One_Reports_Subscriptions.ps1 "[server]/reportserver" "'/reports/title only'" "/"
SharePoint mode syntax:
powershell c:\scripts\List_SSRS_One_Reports_Subscriptions.ps1 "[server]/_vti_bin/reportserver" "'https://[server]/shared documents/title only.rdl'" "https://[server]"
Script:
# Parameters:
# server - server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)
# reportpath - path to report in the report server, including report name e.g. /reports/test report >> pass in "'/reports/title only'"
# site - use "/" for default native mode site
Param
(
[string]$server,
[string]$reportpath,
[string]$site
)
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site);
Write-Host " "
Write-Host " "
Write-Host "----- $reportpath 's Subscriptions: "
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted,Status | where {$_.path -eq $reportpath}
Script: Change ownership of a specific subscription
This script changes the ownership for a specific subscription. You can identify the subscription by the SubscriptionID that you pass into the script. You can use one of the list subscription scripts to determine the correct SubscriptionID.
Native mode syntax:
powershell c:\scripts\Change_SSRS_Owner_One_Subscription.ps1 "[Domain]\[new owner]" "[server]/reportserver" "/" "ac5637a1-9982-4d89-9d69-a72a9c3b3150"
SharePoint mode syntax:
powershell c:\scripts\Change_SSRS_Owner_One_Subscription.ps1 "[Domain]\[new owner]" "[server]/_vti_bin/reportserver" "https://[server]" "9660674b-f020-453f-b1e3-d9ba37624519"
Script:
# Parameters:
# newOwner - DOMAIN\USER that will own the subscriptions you wish to change
# server - server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)
# site - use "/" for default native mode site
# subscriptionID - guid for the single subscription to change
Param(
[string]$newOwner,
[string]$server,
[string]$site,
[string]$subscriptionid
)
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential;
$subscription += $rs2010.ListSubscriptions($site) | where {$_.SubscriptionID -eq $subscriptionid};
Write-Host " "
Write-Host "----- $subscriptionid's Subscription properties: "
$subscription | select Path, report, Description, SubscriptionID, Owner, Status
$rs2010.ChangeSubscriptionOwner($subscription.SubscriptionID, $newOwner)
#refresh the list
$subscription = $rs2010.ListSubscriptions($site) | where {$_.SubscriptionID -eq $subscriptionid}; # use "/" for default native mode site
Write-Host "----- $subscriptionid's Subscription properties: "
$subscription | select Path, report, Description, SubscriptionID, Owner, Status
Script: Run (fire) a single subscription
This script runs a specific subscription by using the FireEvent method. The script immediately runs the subscription regardless of the schedule configured for the subscription. The EventType is matched against the known set of events that are defined in the report server configuration file rsreportserver.config. The script uses the following event type for standard subscriptions:
<Event>
<Type>TimedSubscription</Type>
</Event>
For more information on the configuration file, see RsReportServer.config configuration file.
The script includes delay logic "Start-Sleep -s 6" so there's time after the event fires, for the updated status to be available with the ListSubscription method.
Native mode syntax:
powershell c:\scripts\FireSubscription.ps1 "[server]/reportserver" $null "70366e82-2d3c-4edd-a216-b97e51e26de9"
SharePoint mode syntax:
powershell c:\scripts\FireSubscription.ps1 "[server]/_vti_bin/reportserver" "https://[server]" "c3425c72-580d-423e-805a-41cf9799fd25"
Script:
# Parameters
# server - server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)
# site - use $null for a native mode server
# subscriptionid - subscription guid
Param(
[string]$server,
[string]$site,
[string]$subscriptionid
)
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
#event type is case sensative to what is in the rsreportserver.config
$rs2010.FireEvent("TimedSubscription",$subscriptionid,$site)
Write-Host " "
Write-Host "----- Subscription ($subscriptionid) status: "
#get list of subscriptions and filter to the specific ID to see the Status and LastExecuted
Start-Sleep -s 6 # slight delay in processing so ListSubscription returns the updated Status and LastExecuted
$subscriptions = $rs2010.ListSubscriptions($site);
$subscriptions | select Status, Path, report, Description, Owner, SubscriptionID, EventType, lastexecuted | where {$_.SubscriptionID -eq $subscriptionid}