So you need to change the owner of an SQL Server Reporting Services subscription? No problema. (Why would you need to change it you might ask? Perhaps because you have deactivated the user account that created the subscription and now “strange things are afoot at the Circle K”, and if you know that reference please leave a comment!).

As it turns out, for all of MS SQL’s GUI goodness, there doesn’t seem to be a way to modify the owner via the front end, so you’ll need to use a sql script to update the SSRS subscription owner. First things first: Track down your ReportServer database. It was created when you installed Reporting Services and it holds all the keys to the SSRS kingdom.  Once you’ve tracked down your Report Server you can use our handy query to figure out who owns which report subscriptions to help you confirm you’re on the right track.

Then use the script below to perform your update to the subscriptions table. Important note: if the user you want to change the owner to has never created a report or subscription then they won’t be in the users table. You can create a quick “test” subscription to get the user created and then run the script.

[box type=”info” size=”large”]Tip: We always recommend using a service account for subscriptions to prevent any unexpected behavior that may occur when a user account is disabled.[/box]

DECLARE @OldUser NVARCHAR(260)= 'YOURDOMAIN\oldusername'
DECLARE @NewUser NVARCHAR(260)= 'YOURDOMAIN\serviceaccount'

DECLARE @NewUserID UNIQUEIDENTIFIER

--find the new user id
SELECT
    @NewUserID = uNew.UserID
FROM
    dbo.Users AS uNew
WHERE
    uNew.UserName = @NewUser

--update the userID if i
IF @NewUserID IS NOT NULL 
    BEGIN
        UPDATE
            s
        SET 
            s.OwnerID = @NewUserID
        FROM
            dbo.Subscriptions AS s
        JOIN 
            dbo.Users AS uOld ON s.OwnerID = uOld.UserID
                                 AND uOld.UserName = @OldUser
								 where s.SubscriptionID='752101D7-B11A-425A-81E0-C1A6BE07A066'
    END