Wednesday, May 6, 2015

Changing the SSRS Subscription Owner

When subscriptions are created, owner of that subscription will be the user who creates it. However, when that user account is disabled, subscriptions will be failed and there is no way from the user interface to change it. However these subscriptions are stored in subscriptions table of ReportServer database (default database of the SSRS. This may be different if you have changed it from the SSRS configuration manager). In the subscription table, user id is stored as a unique identifier which you should get from the users table. Update query is shown below.

USE ReportServer

GO

DECLARE @OldUserID uniqueidentifier

DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN\previous_user'

SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN \ new_user’

UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

No restart is of SSRS or SQL Server services are required. Soon after you update the userids subscriptions will start to work.

No comments:

Post a Comment