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.
SELECT
c.Name as report_name,
s.SubscriptionID,
so.UserID as subscription_owner_id,
so.UserName,
s.description,
RS.scheduleid,
‘update s set ownerid=”<<new owner id>>” from Subscriptions s where SubscriptionID=”’+cast(s.SubscriptionID as varchar(100))+”” as [command to update subscription]
FROM
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
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.
I don’t know about a control. Something which may meet your need is to use the ASDI linked server (for example, https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/) and then use that information. What is not good is we only get one department for grouping accounts. Another possibility is xp_logininfo (https://msdn.microsoft.com/en-us/library/ms190369.aspx) which shows all individuals in the group. All that aside, if you have a dedicated AD group which has the intended recipients and use that group in the report’s TO property then the group’s members determine the recipient.