Hi, I have various users creating/modifying SSRS (2012) subscriptions and I need to create a job to update the owners to a service account periodically. I found the below but does not account for multiple original owners. Is there a better script and one that includes everyone? Beside permissions on the new service account, anything else I need to worry about? Thanks!
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