Tip: Change the Owner of SQL Reporting Services Subscription
Here is the scenario:
Your organization has a SQL Reporting Server that generates custom reports based on some of your data. These reports need to be generated each day at noon and then emailed to someone important. So Tom, your current DBA, creates subscriptions for the reports so that they are automatically sent to Mr/Mrs Important. All is well in the world.
Now fast forward six months:
Tom, your DBA, has decided to leave your organization. Like all organizations should do, Toms account is deleted from Active Directory. A few days later, you start getting calls from Mr/Mrs Important wondering why they have not received the emailed reports for the last two days.
It turns out that Tom created the subscriptions using his account, so he is the Owner of the subscriptions. When you look at the history for the subscriptions, you get an error that the subscription owner "DomainA\Tom" does not exist. Now you need to find a way to fix this before Mr/Mrs Important sends you looking for a new job.
OK, so this really happened on a project that I am working on, although the above scenario is made up because the real story is quite lame. The problem is the same though: I needed to change to Owner of the subscription to an existing account.
My first thought was that surely the Report Manager or Management Studio would provide a way to accomplish this. Wrong assumption on my part...there is absolutely no way to do this using the designer tools that interface with SQL Reporting Services. That left me with two options: recreate the subscriptions using a new account (not really efficient), or find a way to change the Owner.
It turns out that you can modify the Owner in the Subscriptions table in the ReportServer database in SQL. This table contains a column called OwnerID that is a foreign key reference to the UserID column in the Users table of the same database. To fix the problem, just replace the old OwnerID with the new one from the Users table that matches the new user that you want to use.
Here is a simple SQL statement that will do the replacement (where OldUser and NewUser are the users that you are trying to swap):
1: DECLARE @OldUserID uniqueidentifier
2: DECLARE @NewUserID uniqueidentifier
3: SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
4: SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'
5: UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID
There could be one final catch: the new user might not exist in the Users table. If that is the case, then you need to add/modify/delete something in the Reporting Server using that account and then it will appear in that table. All I did in this case was a file in the Report Manager and then delete it.