How do I change the owner of a subscription in SQL Server Reporting Services

Question

The previous DBA here set up some SQL Server Reporting Services Reports to run automatically and email the report to users. When he left his accaount was disabled and now they don't work. The status on the subscription reads:

Failure sending mail: The permissions granted to user 'OURDOMAIN\old_DBA_Username' are insufficient for performing this operation.

Is there an easy way to change the owner, I have found the Owner field in the RS database in the subscriptions table and have though of just changing that, but don't want to break our production report service?

The other option of course is to create a new subscription and delete the old, but surly there is a better way.

Answer 1

The solution posted here did the trick for me. Basically you midify the subscription owner on the SSRS database directly by running the script below.

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID