Taking Subscription Control From Reporting Services

Subscriptions in SQL Server Reporting Services (SSRS) is a self-service concept—it means, if you made a subscription, you're the "Administrator" of this specific subscription. In other words, only you can change or eliminate the subscription.

Related: Self-Service Subscriptions

There's another option where specific users with “Administrator” privileges can create a subscription—this privilege enables the Administrator to email the destination textbox in configuration subscriptions so the user can send reports to different users—but only he/she has the control of this subscription to change or eliminate.

With this basic concept in your mind, imagine a hypothetic situation where a user left the company and the notifications (subscriptions) the person created can’t be interrupted because they are very important information?

In this situation, the simpliest procedure is that another user takes control of the subscriptions—and the SQL statements are pretty simple.

First, you have to keep both users, old and new:

DECLARE @OldID Uniqueidentifier,@NewID uniqueidentifierSELECT @OldID = UserID FROM dbo.Users WHERE UserName = 'DOMIANOLDUSER'SELECT @NewID = UserID FROM dbo.Users WHERE UserName = 'DOMIANNEWUSER'UPDATE dbo.Subscriptions SET OwnerID = @NewID WHERE OwnerID = @OldID

IMPORTANT: The query above changes the owner for all the subscriptions—be careful, if you want to take control for a specific subscription, you must to check the following:

DECLARE @SubscriptionID UniqueidentifierSELECT @SubscriptionID = SubscriptionID FROM dbo.Subscriptions WHERE OwnerID = @OldIDUPDATE dbo.Subscriptions SET OwnerID = @NewID WHERE SubscriptionID = @SubscriptionID

I hope this information is helpful.

Comments or questions? Submit your comment below or contact the author, Xavier Sanchez, via email at [email protected].