Page List

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:

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.

This code allows you to search for an Active Directory user by name and return a dynamic list of properties with optional line feed. NOTE: There is a hardcoded userid and password that needs to be changed to a valid AD user login.