Can you change a SSRS subscription owner. SQL, SSRS, Subscription-CodePudding

I have an issue where SSRS subscriptions failing to generate a .Excel report.

The errors I am seeing in the logs are as follows :

Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'Dvardawe2323' is not recognized. e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: library!WindowsService ERROR: Error processing data driven subscription Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'Dvardawe2323' is not recognized.

This is potentially caused by the change of the account that the subscriptions were created with or the 'owner' account.. Using the same account is not an option and I really don't want (or know how to exactly) go through the process of re-creating the subscription.

Is there a way to change the owner of the subscriptions to a different user without recreating the whole process or a 'Copy & paste' of the subscription under a new user ?

Thanks for your help.

CodePudding user response：

There is a known issue if the account used to create a subscription is disabled in active directory, then the subscription will fail. You can update the tables in the background.

I have a project in GitHub that has various system reports for SSRS, e.g. list of subscriptions.

https://github.com/SQL-Server-projects/Reporting-Services-examples

TSQL

/*------------------------------------------------------------------------------ 
| Purpose:  How to Update the owner of deployed reports and subscriptions 
| Note:     SQLCmdMode Script --> in the SSMS menu "Query" | "SQLCMD Mode"
 --------------------------------------------------------------------------------*/

:setvar _server "Server1"
:setvar _user "***username***"
:setvar _password "***password***"
:setvar _database "ReportServer"
:connect $(_server) -U $(_user) -P $(_password)

USE [$(_database)];
GO

:SETVAR OldUser "DOMAIN\OldUserName"
:SETVAR NewUser "DOMAIN\NewUserName"

SET XACT_ABORT ON;
BEGIN TRANSACTION;

PRINT '=====================================================================';
PRINT 'Find subscriptions for user...';
PRINT '=====================================================================';

   WITH 
   user_list
   AS
   (
       SELECT [UserID], [UserName] FROM dbo.[Users] WHERE UserName =  N'$(OldUser)'
   )
   --SELECT 'BEFORE', * FROM user_list
   SELECT 
        'BEFORE'
       , ul.[UserName]
       , sb.*
   FROM 
      dbo.[Subscriptions] sb
      INNER JOIN user_list ul ON sb.[OwnerID] = ul.[UserID];

PRINT '=====================================================================';
PRINT 'Update subscriptions...';
PRINT '=====================================================================';

   WITH 
   user_old
   AS
   (
       SELECT [Old_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(OldUser)'
   )
   , 
   user_new
   AS
   (
       SELECT [New_UserID] = [UserID], UserName FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
   )
   --SELECT 
   --     uo.UserName 
   --   , un.UserName 
   UPDATE 
       dbo.[Subscriptions] 
   SET 
       [OwnerID] = un.[New_UserID]
   FROM 
       dbo.[Subscriptions] sb
       INNER JOIN user_old uo ON sb.[OwnerID] = uo.[Old_UserID]
       , user_new un;

PRINT '=====================================================================';
PRINT 'Update reports...';
PRINT '=====================================================================';

   WITH 
   user_old
   AS
   (
       SELECT [Old_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE UserName =  N'$(OldUser)'
   )
   , 
   user_new
   AS
   (
       SELECT [New_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE UserName =  N'$(NewUser)'
   )
   UPDATE 
       dbo.[Catalog]
   SET 
       [CreatedById] = un.[New_UserID] 
   FROM  
       dbo.[Catalog] AS c
       INNER JOIN user_old uo ON c.[CreatedById] = uo.[Old_UserID]
       , user_new un;
   --WHERE c.[Type] = 2;

   WITH 
   user_old
   AS
   (
       SELECT [Old_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(OldUser)'
   )
   , 
   user_new
   AS
   (
       SELECT [New_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
   )
   UPDATE 
       dbo.[Catalog] 
   SET 
       [ModifiedById] = un.[New_UserID] 
   FROM  
       dbo.[Catalog] AS c
       INNER JOIN user_old uo ON c.[ModifiedById] = uo.[Old_UserID]
       , user_new un;
   --WHERE c.Type = 2;

   WITH 
   user_old
   AS
   (
       SELECT [Old_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(OldUser)'
   )
   , 
   user_new
   AS
   (
       SELECT [New_UserID] = [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
   )
   SELECT 
        c.[CreatedById]
       , c.[ModifiedById]
       , uo.[UserName]
       , un.[UserName] 
       , c.*
   FROM  
       dbo.[Catalog] AS c
       INNER JOIN user_old uo ON c.CreatedById = uo.Old_UserID
       , user_new un;

PRINT '=====================================================================';
PRINT 'Find OLD subscriptions for user...';
PRINT '=====================================================================';

   WITH 
   user_list
   AS
   (
       SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(OldUser)'
   )
   --SELECT 'BEFORE', * FROM user_list
   SELECT 
        'AFTER'
       , ul.[UserName]
       , sb.*
   FROM 
      dbo.[Subscriptions] sb
      INNER JOIN user_list ul ON sb.[OwnerID] = ul.[UserID];

PRINT '=====================================================================';
PRINT 'Find NEW subscriptions for user...';
PRINT '=====================================================================';

   WITH 
   user_list
   AS
   (
       SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
   )
   --SELECT 'BEFORE', * FROM user_list
   SELECT 
        'AFTER'
       , ul.[UserName]
       , sb.*
   FROM 
      dbo.[Subscriptions] sb
      INNER JOIN user_list ul ON sb.[OwnerID] = ul.[UserID];


ROLLBACK TRANSACTION
--COMMIT TRANSACTION   

PRINT '=====================================================================';
PRINT 'Finished...';
PRINT '=====================================================================';

PowerShell

Microsoft PowerShell Script

# Parameters:  
#    currentOwner - DOMAIN\USER that owns the subscriptions you wish to change  
#    newOwner      - DOMAIN\USER that will own the subscriptions you wish to change  
#    server        - server and instance name (e.g. myserver/reportserver, myserver/reportserver_db2, myserver/_vti_bin/reportserver)
  
Param(  
    [string]$currentOwner,  
    [string]$newOwner,  
    [string]$server  
)  
  
$rs2010 = New-WebServiceProxy -Uri "https://$server/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;  
$items = $rs2010.ListChildren("/", $true);  
  
$subscriptions = @();  
  
ForEach ($item in $items)  
{  
    if ($item.TypeName -eq "Report")  
    {  
        $curRepSubs = $rs2010.ListSubscriptions($item.Path);  
        ForEach ($curRepSub in $curRepSubs)  
        {  
            if ($curRepSub.Owner -eq $currentOwner)  
            {  
                $subscriptions  = $curRepSub;  
            }  
        }  
    }  
}  
  
Write-Host " "  
Write-Host " "  
Write-Host -foregroundcolor "green" "-----  $currentOwner's Subscriptions changing ownership to $newOwner : "  
$subscriptions | select SubscriptionID, Owner, Path, Description,  Status  | format-table -AutoSize  
  
ForEach ($sub in $subscriptions)  
{  
    $rs2010.ChangeSubscriptionOwner($sub.SubscriptionID, $newOwner);  
}  
  
$subs2 = @();  
  
ForEach ($item in $items)  
{  
    if ($item.TypeName -eq "Report")  
    {  
        $subs2  = $rs2010.ListSubscriptions($item.Path);  
    }  
}

Microsoft C# Method to change owner

[System.Web.Services.Protocols.SoapDocumentMethod("http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer/ChangeSubscriptionOwner", ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped, RequestNamespace="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer", ResponseNamespace="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer", Use=System.Web.Services.Description.SoapBindingUse.Literal)]
[System.Web.Services.Protocols.SoapHeader("TrustedUserHeaderValue")]
[System.Web.Services.Protocols.SoapHeader("ServerInfoHeaderValue", Direction=System.Web.Services.Protocols.SoapHeaderDirection.Out)]
public void ChangeSubscriptionOwner (string SubscriptionID, string NewOwner);

CodePudding user response：

Got this sorted by manually updating the user IDs in the subscriptions to the new user. I know it's not a "proper" way of doing this but it got me out of trouble until I get more experience with subscriptions

You can either do it manually by going into your report DB table for Subscriptions and edit the owner ID with the ID found in the users table or use the query below found on this forum.

enter code hereDECLARE @OldUserID uniqueidentifier enter code hereDECLARE @NewUserID uniqueidentifier enter code hereSELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN\NewUser' enter code hereSELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser' enter code hereUPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID