Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription
Starting with SQL Server 2008 R2Reporting Services you can programmatically transfer the ownership of a Reporting Services subscription from one user to another. This topic 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 will display the value of new owner. For more information on the object model the PowerShell samples call, see ChangeSubscriptionOwner
| Applies to: Reporting Services Native mode | Reporting Services SharePoint mode |
In this topic:
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 topic 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 do not plan to use that script you can ignore the permission requirements for the FireEvent method.
Native mode:
List Subscriptions: ( HYPERLINK "https://technet.microsoft.com/library/microsoft.reportingservices.interfaces.reportoperation.aspx" ReadSubscription 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 ( HYPERLINK "https://technet.microsoft.com/library/microsoft.sharepoint.spbasepermissions.aspx" 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 .ps1 files, use the name from each example command line syntax.
Open a command prompt with administrative privileges.
Run each script file, using the sample command line syntax provided with each example.
Tested environments
The scripts in this topic were tested on PowerShell version 3 and with the following versions of Reporting Services:
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
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 is also a useful script to simply 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" "http://[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 "http://$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 so 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" "http://[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 "http://$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 "http://$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" "'http://[server]/shared documents/title only.rdl'" "http://[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 "http://$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. The subscription is identified 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" "http://[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 "http://$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 will run a specific subscription using the FireEvent method. The script will immediately run 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 Configuration File.
The script includes delay logic "Start-Sleep -s 6" so there is 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" "http://[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 "http://$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}
See Also
ListSubscriptions ChangeSubscriptionOwner ListChildren FireEvent