Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription

Article
06/23/2014

Starting with SQL Server 2008 R2 Reporting 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

PowerShell related content

Applies to: Reporting Services Native mode | SharePoint mode

In this topic:

How to use the scripts
Script: List the ownership of all subscriptions
Script: List all subscriptions owned by a specific user
Script: Change ownership for all subscriptions owned by a specific user
Script: List all subscriptions associated with a specific report
Script: Change ownership of a specific subscription
Script: Run a single subscription

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:

ReportingService2010.ListSubscriptions Method
ReportingService2010.ChangeSubscriptionOwner Method
ReportingService2010.ListChildren
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: (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 (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 2012
SQL Server 2008 R2

Arrow icon used with Back to Top link Top

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.

Arrow icon used with Back to Top link Top

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}

Arrow icon used with Back to Top link Top

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 $previousOwner)
            {
                $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);
    }
}

Arrow icon used with Back to Top link Top

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}

Arrow icon used with Back to Top link Top

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

Arrow icon used with Back to Top link Top

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}

Arrow icon used with Back to Top link Top

Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription

How to use the scripts

Permissions

Script usage

Script: List the ownership of all subscriptions

Script: List all subscriptions owned by a specific user

Script: Change ownership for all subscriptions owned by a specific user

Script: List all subscriptions associated with a specific report

Script: Change ownership of a specific subscription

Script: Run (fire) a single subscription

See Also

Reference

Additional resources