Recently a DBA left the company and before his final day we disabled his login account to make sure that nothing broke (i.e. no jobs were running under his account, etc.).  The next couple days everything seemed fine.  Then after he left we actually deleted his account.  After deleting his account every SSRS report subscription that was originally created by him stopped working.  Some of them gave error messages that indicated some type of problem with the user account while others had very vague error messages.  The bottom line is that they ran fine when the AD account existed (regarless of the state of the account); but, the fail once the account is removed.  The initial fix was simply to create a new version of the subscription under a different user account; however, there were so many that this would be a very tedious and time consuming process. 

As an alternative the attached script can be used to change the subscription owner of all subscriptions belonging to a specific user. To execute the Script, please follow these steps:

1. Open Command line and Change Directory to this location “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn”
2. Execute the following command:

• rs -i <Path to ChangeSubscriptionOwner.rss Script> -s <ReportServer URL> -e Mgmt2010 -v varOwner="<CurrentSubscriptionOwner>" -v varNewOwner="<NewSubscriptionOwner>"

Example:

• rs -i D:\ChangeSubscriptionOwner.rss -s http://myserver/reportserver -e Mgmt2010 -v varOwner="mydomain\origuserid" -v varNewOwner="mydomain\newuserid"

This will change all the Subscriptions owned by "mydomain\originaluserid" to "mydomain\newuserid".

Note: Please Execute this script on the Reporting Services Server.

Note 2: This script and should be used only when Good ReportServer and ReportServerTempDB backups are available. As a good practice, you should also take a backup of the Reporting Services EncryptionKey.

BegDate: =CDate("01/" & Month(Now) & "/" & Year(Now))

EndDate: =DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, Parameters!BegDate.Value))

The following syntax will stack two values on top of each other in a single text box:

=Fields!FieldName1.Value & Chr(13) & Chr(10) & Fields!FieldName2.Value