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