SSRS Subscription Owners

We have to be sure subscription owners are not going to leave and make the subscriptions they own fail. Here is a way to make this information visible easily. For the reporting group I made a stored procedure which takes as arguments the current owner, desired owner, and other info to do the work.

c.Name as report_name,
so.UserID as subscription_owner_id,
‘update s set ownerid=”<<new owner id>>” from Subscriptions s where SubscriptionID=”’+cast(s.SubscriptionID as varchar(100))+”” as [command to update subscription]
ReportServer.dbo.[Catalog] c
JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer.dbo.ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
JOIN ReportServer.dbo.Users so on s.OwnerID = so.UserID

This entry was posted in SQL, SSRS. Bookmark the permalink.

2 Responses to SSRS Subscription Owners

  1. Ashu says:

    I wonder does SSRS have an exstienon for the TO field ;to leverage Active Directory so a subscription manager could select individual emails from a list instead of having to type it in.I’m new to SSRS (using it on 2005) and it looks sort of kludge-ey to have to manually enter addresses. Wondering if I have to write a custom control that can access AD and build the parameters list for the subscription table. Good article lots of useful detail for someone new to SSRS.

Leave a Reply

Your email address will not be published. Required fields are marked *