locked
How do I transfer "My Subscriptions"? RRS feed

  • 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

    Tuesday, August 1, 2006 7:45 PM

Answers

  • 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.

    Thursday, August 3, 2006 5:47 PM

All replies

  • 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

    Wednesday, August 2, 2006 6:15 AM
  • 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

     

    Wednesday, August 2, 2006 6:20 PM
  • 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.
    Thursday, August 3, 2006 4:34 AM
  • 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

    Thursday, August 3, 2006 1:53 PM
  • 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??

    Thursday, August 3, 2006 3:56 PM
  • 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.

    Thursday, August 3, 2006 5:47 PM
  • 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

    Tuesday, April 9, 2013 1:01 AM