none
How do I change a subscription owner? RRS feed

  • Question

  • On some preexisting subscriptions there is a single person that is the owner of the subscription. Is there a way to change the ownername to someone else without having to delete the whole subscription and reenter it under the new owner name? I am aware that you can use groups, but these subscriptions already exist and have a single owner.

    Thank you for any help.

    Friday, December 1, 2006 9:27 PM

All replies

  • Hi,

    As far as I know, there is no direct way to do that.

    My approach will be to script out the former user subscriptions, to log in as the new user and reply the scripts.

    HTH,

    Jordi Rambla
    MVP SQL Server (Reporting Services)
    Certia - rambla informàtica (http://www.certia.net)
    Solid Quality Learning (http://www.solidqualitylearning.com)

     

    Sunday, December 3, 2006 9:21 AM
  • Kim-

    Look into the UpdateSubscription stored procedure in the Reportserver database.  You may be able to do it there, as there is no way to do it through code using the web service API...the OwnerId property is ReadOnly.

    But you'll have to make sure that the new owner has access to that report to change the subscription to the new user.

    Regards,
    Scott
    Monday, December 4, 2006 5:44 PM
  • Thank you Scott. Where is that exactly?
    Monday, December 4, 2006 9:03 PM
  • Kim-

    It's located in the ReportServer database.

    Most likely you would have to use the ListChildren method to see if the new user has authorization to view the report. If yes, then you could write some code and SQL to check for the specific subscription you are looking to change. The three tables you would be looking at are Catalog, Users, and Subscriptions. You'll have to extract some data and then you can call the UpdateSubscription stored procedure if everything looks ok.

    Maybe something like this (psuedocode):

    Imports System
    Imports System.Web.Services.Protocols


    Class MySubscriptions
        Private m_server as ReportServer()

        Private Sub GetSubscriptionInfo()
            Dim mySubId As String
            Dim reportPath As String
           
    Dim subscriptions As Subscription() = Nothing

                   
            Try
                m_server.Url = "http://myserver/reportserver/reportservice.asmx"
                m_server.LogonUser("OriginalOwnerOrAdmin", "Pwd", Nothing)

               
    subscriptions =
    m_server.ListSubscriptions("/SampleReports/My Report", "OriginalOwnerOrAdmin")

               
    If Not (subscriptions Is Nothing) Then
                    'Loop through to get your subscription here to get specific info
                    mySubId = subscriptions(arrayNumber).ID
                    reportPath = subscriptions(
    arrayNumber).Path
                End If

                ChangeSubscriptionOwner(mySubId, reportPath)
            Catch
                '::: Put Error Handler Here :::
            End Try
        End Sub

        Private Sub ChangeSubscriptionOwner(ByVal id As String, ByVal path As String)
            Dim userOk as Boolean
            Dim reportId as String
            Dim rs As New ReportServer()
            Dim item As CatalogItem
            Dim items As CatalogItem()

            Try

                '::: Set ReportServer URL and logon the user you want to be the new owner :::
                m_server.Url = "http://myserver/reportserver/reportservice.asmx"
                m_server.LogonUser("MyUser", "MyPwd", Nothing)

                '::: ReportServer.ListChildren returns an array of CatalogItems :::
                items = rs.ListChildren("ReportPath", True)

                '::: See if the user has access to the report in the subscription :::
                For Each item in items
       
                If item = path Then
           
               userOk = True
           
               reportId = item.ID
           
                Exit For
       
                End If
                Next

                '::: If User has access then call the UpdateSubscription with the new owner :::
                If userOk = True Then
       
                 'Find MyUser in Users table and get UserID
       
                'Run UpdateSubscription stored procedure with SubscriptionId = id and OwnerID = UserId
       
             End If
            Catch
                '::: Put Error Handler Here :::
            End Try
        End Sub

    End Class

    Note I haven't done this, but it looks like the only way that you'll be able to do this without deleting and re-creating. Hope this points you in the right direction.

    Regards,
    Scott
    Tuesday, December 5, 2006 3:58 PM
  • Thank you Scott!!
    Tuesday, December 5, 2006 4:03 PM
  • Another way to update the owner of subscription is by updating the OwnerID column in Subscriptions table in ReportServer db. See steps below:

     

    IMPORTANT: Before you proceed with these steps , backup the Report Server DB.

     

    Step 1:

    -------------------------------------------------

    -- Get UID of the owner that needs to be changed

    -------------------------------------------------

    DECLARE @RC int

    DECLARE @UserName nvarchar(260)

    DECLARE @AuthType int

    DECLARE @UserID uniqueidentifier

    -- Set parameter values here.

    SET @UserName ='benny.austin'

    SET @AuthType=1

    EXECUTE @RC = [ReportServer$SQL2005].[dbo].[GetUserIDByName]

    @UserName

    ,@AuthType

    ,@UserID OUTPUT

    select @UserName,@UserID

     

    Step 2:

    -------------------------------------------------

    -- Get UID of the new owner

    -------------------------------------------------

    -- Set parameter values here.

    SET @UserName ='Administrator'

    SET @AuthType=1

    EXECUTE @RC = [ReportServer$SQL2005].[dbo].[GetUserIDByName]

    @UserName

    ,@AuthType

    ,@UserID OUTPUT

    select @UserName,@UserID

     

    Step 3:

     

    --Good idea to backup these records before update

    select * from subscriptions

    WHERE OWNERID = '06D1A739-5C5F-443E-A15A-BDB7CBECAF89'

     

    Step 4:

     

    --Update to new owner

    Update Subscriptions

    SET OWNERID='0E01DB91-75A2-4D7A-90B6-05C9DDB2D852'

    WHERE OWNERID ='06D1A739-5C5F-443E-A15A-BDB7CBECAF89'

    Wednesday, October 3, 2007 9:37 PM
  • This was very helpful. THX!
    Thursday, January 20, 2011 8:50 PM
  • For the SQL Server 2008 R2 release, there is now the  ReportingService2010.ChangeSubscriptionOwner Method.

    Please see the topic http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.changesubscriptionowner.aspx

    Craig


    -Craig (SSRS UE Team) // This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, January 20, 2011 9:26 PM
    Moderator