How do I transfer "My Subscriptions"?

Question

I am an administrator in Reporting Services 2000/2005 (mixed environment) and have set up several report subscriptions.  I see all of the subscriptions under "My Subscriptions" which provides an easy way to see and manage all the subscriptions.

Now we have a few other administrators on my team, and they also want to see all the subscriptions in the environment, but when they click "My Subscriptions" their list is empty.  I have set them up with the same security role as me, but this doesn't seem to matter.

How can I provide a way for the admins to see all subscriptions on all reports in the report server, even if each subscription may be set up by a different user?

Thanks

Kory

Answer 1

Did you look at the RSScripter tool (http://www.sqldbatips.com/showarticle.asp?ID=62)? It can show all subscriptions associated for a particular report.

Btw: the RSScripter tool is based on the RS SOAP API - so you could write a small application that connects to a report server and shows all the specific information you would like to see about subscriptions.

-- Robert

Answer 2

Rob-

I checked out the scripter tool (v2.0.0.6), and I didn't see any subscriptions, so I looked in the Options dialog.  On the Reports tab, the option to see Subscriptions was unchecked by default, so I checked it, and refreshed the catalog... but I still don't see any subscriptions.  I looked under each report, and no new node exists for the subscription, and I also looked at the bottom of the treeview to see if Subscriptions had their own folder, but this didn't show up either.

Where do I look?

-Kory

 

Answer 3

I am not familiar with the Scripter tool, but if you are comfortable with using the Soap API you could write an app to call ListSubscriptions with no username or report path.  This will return the list of all subscriptions in the system that you have permission to see.  There is no way to view a subscription you do not have permission to see.

Answer 4

Same problem here with multiple admins that can't see My Subscriptions.

I wrote a report from this query to calalog reports.

 

SELECT     c.Name, c.Type, c.Description, u.UserName AS CreatedBy, c.CreationDate, c.ModifiedDate, s.Description AS Subscription,
                      s.DeliveryExtension AS SubscriptionDelivery, d.Name AS DataSource, s.LastStatus, s.LastRunTime, s.Parameters, sch.StartDate AS ScheduleStarted,
                      sch.LastRunTime AS LastSubRun, sch.NextRunTime, c.Path
FROM         Catalog c LEFT OUTER JOIN
                      Subscriptions s ON c.ItemID = s.Report_OID INNER JOIN
                      DataSource d ON c.ItemID = d.ItemID LEFT OUTER JOIN
                      Users u ON u.UserID = c.CreatedByID LEFT OUTER JOIN
                      ReportSchedule rs ON c.ItemID = rs.ReportID LEFT OUTER JOIN
                      Schedule sch ON rs.ScheduleID = sch.ScheduleID
WHERE     (c.Type = 2)
ORDER BY c.Name

Answer 5

I was informed by Jasper Smith, author of the RSScripter utility, that subscriptions do not show up in the UI, only the scripts generated (after you check the "Include Subscriptions" option)

But I can't believe there isn't a practical way to administer subscriptions globally.  You would think a user with Admin privs would be able to see everyones subscriptions, reports, properties, etc.

Will a service pack give this ability in the future??

Answer 6

It is not likely that subscriptions will become a non-permissioned item.  Currently Report Server does not have a concept of an Admin.  If such a concept were to ever show up then I would see them having permission to see all subscriptions.  There is nothing preventing you from setting up your security such that a group of people always have permission to see all subscriptions.

The problem currently is the Report Server does not ship with a way to view all subscriptions (that you have permission to see).  As I stated earlier you could write a quick app to do this, or if you wish to by-pass security (not-recommended :) ) you could create a report on the subscription table as was suggested by someone earlier.

Answer 7

Thanks to the folks for the query. I noticed that I wasn't getting the output I wanted with HerbD's query. I figured it had to do with the ordering of the inner and left outer joins. I would invite you to consider the following tweaked query if the analysis of data doesn't really tally with herb's query for you

select

u

.UserName,

c

.name,

sch

.[StartDate],

sch

.[NextRunTime],

sch

.[LastRunTime],

[DaysInterval]

,

[WeeksInterval]

,

s

.[SubscriptionID],

rsc

.[ScheduleID]asrptschscheduleid,

sch

.[ScheduleID]asschscheduleid

from

[dbo].[Subscriptions]s

left

outerjoin[dbo].[ReportSchedule]rsc

on

s.[SubscriptionID]=rsc.[SubscriptionID]

left

outerjoin[dbo].[Schedule]sch

on

sch.[ScheduleID]=rsc.[ScheduleID]

left

outerjoin[dbo].[Users]u

on

u.UserID=s.[OwnerID]

left

outerjoindbo.Catalogc

on

c.ItemID=rsc.ReportID