Change The Owner Of An SSRS Subscription: The Simple Way

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.

Strange things are afoot in SSRS...
Strange things are afoot in the SSRS...

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'


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

--update the userID if i
            s.OwnerID = @NewUserID
            dbo.Subscriptions AS s
            dbo.Users AS uOld ON s.OwnerID = uOld.UserID
                                 AND uOld.UserName = @OldUser
								 where s.SubscriptionID='752101D7-B11A-425A-81E0-C1A6BE07A066'

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *