question

caoneill avatar image
caoneill asked

SSRS - Script to update owner of all subscriptions to a service account

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

ssrs-2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.