none
Change the Owner of SQL Reporting Services Subscription RRS feed

  • Question

  • So, I am having this odd issue after our Sysadmin changed my user name from what it used to be to something new.

    Now, subscriptions for reports that I had created for users are not working anymore. And it gives me following error,

    Failure sending mail: The user or group name 'DOMAIN\MyUser' is not recognized.Mail will not be resent.


    A quick Google, landed me on this old tip from MSDN about changing owner of report. According to that tip all I have to do is to update all entries in  "subscriptions"  table from older user to new user id, but for that new user should exist in "users" table  but in my case it didn't exist. So as suggested in very bottom of that same tip, I created (and deleted) bunch of items using Report Server Manager. But it didn't create my new user.

    Then, I removed old subscription and created new subscription. But it still shows my old username as owner of that subscription.

    So my question is, how do I change Owner of Subscription ?

    UPDATE:

    I forgot to mention that I am using SQL Server 2008 SP3

    TIA,

    Jack



    • Edited by Jack Locke Wednesday, March 5, 2014 7:37 PM
    Wednesday, March 5, 2014 3:18 PM

All replies

  • Microsoft has an API call that should do it for you...

    http://technet.microsoft.com/en-us/library/reportservice2010.reportingservice2010.changesubscriptionowner.aspx

    You could use that in conjunction with the ListSubscriptions API call to get all the subscriptions and loop through them.  This could be done with PowerShell.  Below is a rough script that is untested, but could get you started.

    # Used With NTLM (Integrated Authentication) on SSRS
    $ssrsProxy = New-WebServiceProxy -Uri "<enter URL here>/reportserver/ReportService2010.asmx" -UseDefaultCredential
           
    #Get the subscriptions
    $Subscriptions = $ssrsProxy.ListSubscriptions("/")
    
    ForEach ($Subscription in $Subscriptions)
    {
        If ($Subscription.Owner -eq "<Enter Old UserName here>")
        {
            $ssrsProxy.ChangeSubscriptionOwner($Subscription.SubscriptionID,"<Enter New Username Here>")
        }
    }


    • Edited by JJordheim Wednesday, March 5, 2014 3:30 PM
    Wednesday, March 5, 2014 3:29 PM
  • Thanks for quick response.

    After your post, I tried to use your script with modifications of URI but it looks like that ListSubscriptions method takes two arguments ... Report Path and User Name but it doesn't work even if I provide both of them.

    I may be missing something because I am not so good with PS (and also not much familiar with .NET stack)

    Any other suggestion ?

    Wednesday, March 5, 2014 5:01 PM

  • Refer-

    http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

     DECLARE @OldUserID uniqueidentifier
     DECLARE @NewUserID uniqueidentifier
     SELECT @OldUserID =UserID FROM dbo.Users WHERE UserID ='HQ\abc'
     SELECT  @NewUserID=UserID FROM dbo.Users WHERE UserName = 'HQ\def'
     UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID
     
    
    -Prashanth

    Wednesday, March 5, 2014 5:18 PM
  • ListSubscriptions only takes one argument...

    http://technet.microsoft.com/en-us/library/reportservice2010.reportingservice2010.listsubscriptions.aspx

    If i grab the first 4 lines of that script then write the subscriptions variable to screen i can see all my subscriptions in our system.

    As mentioned the script is meant to get you started.  It could be failing on the authentication.  So depending on how your system is configured you might need to do something different in how you connect.  You'll probably have to do a little research to get everything to match up with your environment and configuration.

    Wednesday, March 5, 2014 5:32 PM

  • Refer-

    http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

     DECLARE @OldUserID uniqueidentifier
     DECLARE @NewUserID uniqueidentifier
     SELECT @OldUserID =UserID FROM dbo.Users WHERE UserID ='HQ\abc'
     SELECT  @NewUserID=UserID FROM dbo.Users WHERE UserName = 'HQ\def'
     UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID
     
    -Prashanth


    Before posting my question here, I actually looked at that same page. And tried what is suggested in that post but I didn't help me.
    Wednesday, March 5, 2014 7:38 PM
  • ListSubscriptions only takes one argument...

    http://technet.microsoft.com/en-us/library/reportservice2010.reportingservice2010.listsubscriptions.aspx

    If i grab the first 4 lines of that script then write the subscriptions variable to screen i can see all my subscriptions in our system.

    As mentioned the script is meant to get you started.  It could be failing on the authentication.  So depending on how your system is configured you might need to do something different in how you connect.  You'll probably have to do a little research to get everything to match up with your environment and configuration.

    I am not sure if it has something to do or not ... but I am using SQL Server 2008 SP3. And so if I use code like below,

    $ssrsProxy = New-WebServiceProxy -Uri "http://<report server>/reportserver/reportservice2005.asmx" -UseDefaultCredential
    $ssrsProxy | Get-Member

    Then I can see that ListSubscriptions has following definition, and it shows that it is actually expecting 2 arguments.

    ListSubscriptions                            Method     Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.reportservice2005_asmx.Subscription[] ListSubscriptions(string Report, string Owner)
    May be it is because I am using SQL Server 2008 ... not sure.



    • Edited by Jack Locke Wednesday, March 5, 2014 7:53 PM
    Wednesday, March 5, 2014 7:52 PM
  • Yeah... my code was working off a different web service...  The older version isn't as easy to work with, but I crafted something that might work.  See the code below.  What you should probably do is comment out the inner for loop (i.e., using #) that is going to do the change and display what's in the $Subscriptions object (i.e., add a line under the List Subscriptions call... $Subscriptions) to make sure you are getting back the subscriptions for the user in question.

    # Used With NTLM (Integrated Authentication) on SSRS
    $ssrsProxy = New-WebServiceProxy -Uri "<Enter URL Here>/reportserver/ReportService2005.asmx" -UseDefaultCredential
           
    #Get the subscriptions
    $Reports = $ssrsProxy.ListChildren("/",$true) | Where-Object {$_.Type -eq "Report"}
    ForEach ($Report in $Reports)
    {
        $Subscriptions = $ssrsProxy.ListSubscriptions($Report.Path, "<Enter Old User Here>")
        ForEach ($Subscription in $Subscriptions)
        {
              $ssrsProxy.ChangeSubscriptionOwner($Subscription.SubscriptionID,"<Enter New Username Here>")
        }
    }



    • Edited by JJordheim Wednesday, March 5, 2014 8:53 PM
    Wednesday, March 5, 2014 8:51 PM
  • Yeah... my code was working off a different web service...  The older version isn't as easy to work with, but I crafted something that might work.  See the code below.  What you should probably do is comment out the inner for loop (i.e., using #) that is going to do the change and play display what's in the $Subscriptions object (i.e., add a line under the List Subscriptions call... $Subscriptions) to make sure you are getting back the subscriptions for the user in question.

    # Used With NTLM (Integrated Authentication) on SSRS
    $ssrsProxy = New-WebServiceProxy -Uri "<Enter URL Here>/reportserver/ReportService2005.asmx" -UseDefaultCredential
           
    #Get the subscriptions
    $Reports = $ssrsProxy.ListChildren("/",$true) | Where-Object {$_.Type -eq "Report"}
    ForEach ($Report in $Reports)
    {
        $Subscriptions = $ssrsProxy.ListSubscriptions($Report.Path, "<Enter Old User Here>")
        ForEach ($Subscription in $Subscriptions)
        {
              $ssrsProxy.ChangeSubscriptionOwner($Subscription.SubscriptionID,"<Enter New Username Here>")
        }
    }


    Thanks for getting script fixed for me.

    Now I can see report name for which I need to modify owner. But when I run the script it says $ssrsProxy doesn't contain a method named 'ChangeSubscriptionOwner'

    Wednesday, March 5, 2014 9:19 PM
  • Yeah... that's a bummer... I just looked and that method isn't supported with the 2005 web service.  Sorry i lead you down that rabbit hole...  They don't appear to have an alternative solution other than maybe deleting and recreating the subscriptions (manually or programmatically) or keep trying to get the database script to work...
    Wednesday, March 5, 2014 10:07 PM
  • Actually out of frustration ... I did created another subscription yesterday and then removed old one. But for some odd reasons it still shows my old user name as owner of this new subscription and due to that it still fails to send email !!

    More strange thing is, I removed old report and uploaded new RPT file and it still shows that this report was modified by my old user name (however, I didn't remove data source or data set for that report)

    So weird.

    Thursday, March 6, 2014 1:51 PM
  • Just a thought... I wonder if your SID didn't change in AD...  Which might explain why when you create something it maps to the user entry with your old username in the Users table.  What you might consider trying is just updating the record in the Users table with the correct username...

    UPDATE Users

    SET UserName = '<New Username>'

    WHERE UserName = '<Old Username>'


    • Edited by JJordheim Thursday, March 6, 2014 2:03 PM
    Thursday, March 6, 2014 2:02 PM
  • Just a thought... I wonder if your SID didn't change in AD...  Which might explain why when you create something it maps to the user entry with your old username in the Users table.  What you might consider trying is just updating the record in the Users table with the correct username...

    UPDATE Users

    SET UserName = '<New Username>'

    WHERE UserName = '<Old Username>'


    I don't know much about AD, but may be it doesn't change SID once it gets created (even when user is modified)

    For some reasons my user name in USERS table is myOldUserName@domain.com format ... and all other users are Domain\UserName format. But inside Report Manager or anywhere in SSRS, I see my user as Domain\MyNewUserName

    I know that both user@domain.com and Domain\User are same, but just wondering why only my user is like that ... and ... weather or not I will run into any issue if I simply change the userName.

    I just want to be sure about this because it is live system.

    Thursday, March 6, 2014 2:15 PM
  • The following article may help...

    Use PowerShell to Change and List Reporting Services Subscription Owners and Run a Subscription

    http://msdn.microsoft.com/en-us/library/dn747196(d=printer,v=sql.120).aspx


    -Craig (SQL Server User Education Team) // This posting is provided AS IS with no warranties, and confers no rights.

    Tuesday, September 23, 2014 8:30 PM
    Moderator
  • Hello Jack.

    I've had exactly the same problem you are having right now.

    I didn't manage to get it working with my own user anymore, so I had to change the owner of the subscriptions to another domain user. You'll have to change the subscription owner whenever you create a new subscription using your own user too.

    To solve this problem, you need update the subscriptions table in the reporting services database, changing the SID of the owner to a different, existing user.


    Edit: To be more specific, you need to update the OwnerID field of the Subscriptions table to an existing value in the UserID field of the Users table, where UserID is different from the current UserID (yours).
    Tuesday, September 23, 2014 8:58 PM
  • Samir advice works ! Thanks !

    I'm more familiar with SQL.

    Thursday, November 19, 2015 12:42 PM
  • I also had this issue happen to me when a person who created a subscription got removed from AD because they're no longer with the company.

    I wrote a stored procedure so that this would never happen again by setting ALL subscriptions to the SQL Service account.  It depends on having a database called 'dba' on the report server.  

    Installation:

    1. Add your SQL Server service account as a System Administrator to your SSRS instance

    2. Make sure your SQL Server service account is a sysadmin

    3. Add a dba database (or change the SP to use whatever your dba database name is)

    4. Run the following script to add the SP

    5. Add a job to call exec dba.dbo.usp_DBA_SSRS_SetSubscriptionOwners 'Domain\ServiceAccountName' (update Domain\ServiceAccountName with your service account name).  Schedule the job to run every day (or X hours).

    The stored procedure will:

    1. Check the service account is in the ReportServer.dbo.Users table.  If it doesn't exist, nothing happens.

    2. Creates a table in your dba database called [DBA].[dbo].[Backup_ReportServer_dbo_Subscription_Owners] (if it doesn't exist).  This table will have a backup the 'Before' and 'After' values for the OwnerID that is changed in the Subscriptions table.

    2. Will update ALL subscriptions to the service account passed in by updating the the OwnerID in the ReportServer.dbo.Subscriptions to the ServiceAccount you passed in (found in the ReportServer.dbo.Users table).

    Stored Procedure code:

    USE DBA
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Joel Foudy
    -- Create date: 12/6/2019
    -- Description:	Updates all SubscriptionOwners to a Service account, also backs up the before/after changes to a table in the DBA db.
    -- 
    -- Example call:
    -- exec dba.dbo.usp_DBA_SSRS_SetSubscriptionOwners 'Domain\ServiceAccountName'
    -- =============================================
    CREATE PROCEDURE [dbo].[usp_DBA_SSRS_SetSubscriptionOwners]	
    	@ServiceAccountName varchar(1000) 
    AS
    BEGIN
    	--NOTE: Prerequiste, you MUST add the service account as a System Administrator to SSRS instance.
    
    	--DECLARE @ServiceAccountName varchar(1000) = 'Domain\ServiceAccountName'  --For testing purposes so you can run inline query if needed.
    	
    	--Check if we can find the service account in the users table.  If not skip the update
    	if exists ( select userid from ReportServer.dbo.users where  username = @ServiceAccountName)
    	BEGIN
    		
    		PRINT 'Found Service Account'
    
    		DECLARE @UserId varchar(1000) = (select userid from ReportServer.dbo.users where  username = @ServiceAccountName)
    
    		IF OBJECT_ID (N'Backup_ReportServer_dbo_Subscription_Owners', N'U') IS NULL -- if table doesn't exist
    		BEGIN
    			PRINT 'Table doesn''t exist'
    		
    			CREATE TABLE [DBA].[dbo].[Backup_ReportServer_dbo_Subscription_Owners]
    			(
    				[SubscriptionBackupId] [int] IDENTITY(1,1) NOT NULL,
    				[SubscriptionID] [uniqueidentifier] NOT NULL,
    				[OwnerID] [uniqueidentifier] NOT NULL,
    				[Report_OID] [uniqueidentifier] NOT NULL,
    				[Locale] [nvarchar](128) NOT NULL,
    				[InactiveFlags] [int] NOT NULL,
    				[ExtensionSettings] [ntext] NULL,
    				[ModifiedByID] [uniqueidentifier] NOT NULL,
    				[ModifiedDate] [datetime] NOT NULL,
    				[Description] [nvarchar](512) NULL,
    				[LastStatus] [nvarchar](260) NULL,
    				[EventType] [nvarchar](260) NOT NULL,
    				[MatchData] [ntext] NULL,
    				[LastRunTime] [datetime] NULL,
    				[Parameters] [ntext] NULL,
    				[DataSettings] [ntext] NULL,
    				[DeliveryExtension] [nvarchar](260) NULL,
    				[Version] [int] NOT NULL,
    				[ReportZone] [int] NOT NULL,
    				[ChangedDate] [DateTime] NOT NULL,
    				[ChangeType] [varchar] (20) NOT NULL,
    			 CONSTRAINT [PK_SubscriptionBackupId] PRIMARY KEY CLUSTERED 
    			(
    				[SubscriptionBackupId] ASC
    			)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    			) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    	
    		END --check if 
    		
    		--Backup all subscription rows where the Owner doesn't equal our Service account.
    		INSERT INTO DBA.dbo.Backup_ReportServer_dbo_Subscription_Owners
    			(
    			[SubscriptionID]
    		  ,[OwnerID]  
    		  ,[Report_OID]
    		  ,[Locale]
    		  ,[InactiveFlags]
    		  ,[ExtensionSettings]
    		  ,[ModifiedByID]
    		  ,[ModifiedDate]
    		  ,[Description]
    		  ,[LastStatus]
    		  ,[EventType]
    		  ,[MatchData]
    		  ,[LastRunTime]
    		  ,[Parameters]
    		  ,[DataSettings]
    		  ,[DeliveryExtension]
    		  ,[Version]
    		  ,[ReportZone]
    		  ,[ChangedDate]
    		  ,[ChangeType])
    			select *, GetDate() as ChangedDate, 'OldValue' as ChangeType
    			--into DBA.dbo.Backup_ReportServer_dbo_Subscription_Owners
    			from ReportServer.dbo.Subscriptions 
    			where OwnerID <> @UserId
    
    			--Add New Value for all subscription rows where the Owner doesn't equal our Service account.
    			INSERT INTO DBA.dbo.Backup_ReportServer_dbo_Subscription_Owners
    			(
    			[SubscriptionID]
    		  ,[OwnerID]  
    		  ,[Report_OID]
    		  ,[Locale]
    		  ,[InactiveFlags]
    		  ,[ExtensionSettings]
    		  ,[ModifiedByID]
    		  ,[ModifiedDate]
    		  ,[Description]
    		  ,[LastStatus]
    		  ,[EventType]
    		  ,[MatchData]
    		  ,[LastRunTime]
    		  ,[Parameters]
    		  ,[DataSettings]
    		  ,[DeliveryExtension]
    		  ,[Version]
    		  ,[ReportZone]
    		  ,[ChangedDate]
    		  ,[ChangeType])
    		  select 
    			[SubscriptionID]
    		  ,@UserId  --NEW Value to be updated
    		  ,[Report_OID]
    		  ,[Locale]
    		  ,[InactiveFlags]
    		  ,[ExtensionSettings]
    		  ,[ModifiedByID]
    		  ,[ModifiedDate]
    		  ,[Description]
    		  ,[LastStatus]
    		  ,[EventType]
    		  ,[MatchData]
    		  ,[LastRunTime]
    		  ,[Parameters]
    		  ,[DataSettings]
    		  ,[DeliveryExtension]
    		  ,[Version]
    		  ,[ReportZone]
    		  , GetDate() as ChangedDate, 'NewValue' as ChangeType		
    		  from ReportServer.dbo.Subscriptions 
    		  where OwnerID <> @UserId
    
    		--Update all subscriptions to the service account
    		update ReportServer.dbo.Subscriptions 
    		set OwnerID = @UserId
    		where OwnerID <> @UserId
    
    	END --Check if user exists
    END
    GO

     

    • Edited by Sparqs Friday, December 6, 2019 4:53 PM
    Friday, December 6, 2019 4:48 PM