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

If you are reading this, you are most likely seeing the above error message in one or more of your report subscriptions for SSRS. Maybe an analyst or DBA has left your company, and their active directory account was recently removed or disabled. We encountered this same issue recently with a client of ours and the fix was relatively simple, in our case. The problem lies with the subscription owner, which by default is set to the user account of the person that created the subscription. But, at least in our case, trying to change the owner from within the web portal for SSRS failed with an error similar to the one seen in the subscription history. Here is the solution that worked for us.

The first step is to backup your SSRS ReportServer database! You should already have ongoing backups that meet the data retention requirements of your enterprise, and you should be testing the viability of those backups regularly. That said, here is the script to address this issue (an explanation follows after). The script will update all records that have the same owner as the specified report:

DECLARE 
 @ReportName VarChar(50)='Daily Orders',  --Name of the report
 @NewUserName VarChar(50)='DOMAIN\NewUser', --New Owner Username
 @OldUserID UniqueIdentifier,
 @NewUserID UniqueIdentifier

DROP TABLE IF EXISTS #T
SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
INTO #T
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
WHERE C.Name = @ReportName
AND S.LastStatus LIKE 'Failure sending mail: The user or group name%'

SELECT * FROM #T

SELECT TOP 1 @OldUserID=OwnerID FROM #T
SELECT TOP 1 @NewUserID=UserID FROM Users WHERE UserName=@NewUserName

BEGIN TRAN
DROP TABLE IF EXISTS #O
CREATE TABLE #O (SubscriptionID UniqueIdentifier)

SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
WHERE OwnerID=@OldUserID

UPDATE S
SET OwnerID=@NewUserID
OUTPUT deleted.SubscriptionID INTO #O
FROM Subscriptions S
WHERE OwnerID=@OldUserID
 
SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
 INNER JOIN #O
  ON S.SubscriptionID=O.SubscriptionID
ROLLBACK TRAN

The script will update the owner and query the results of the update before rolling back the changes. This allows the results to be verified before actually committing the changes. It is recommended to look at the number of affected rows in the UPDATE statement, in addition to the results of the subsequent SELECT statement. To commit the changes, edit "ROLLBACK TRAN" to "COMMIT TRAN" and execute the script again.

Variables

First the variables must be defined:

DECLARE 
 @ReportName VarChar(50)='Daily Orders',  --Name of the report
 @NewUserName VarChar(50)='DOMAIN\NewUser', --New Owner Username
 @OldUserID UniqueIdentifier,
 @NewUserID UniqueIdentifier

@ReportName: The name of the report that has failing subscriptions.

@NewUserName: The user name of the account that will be the new owner (we recommend using a service account, to avoid having the same problem again in the future*).

@OldUserID: Do not provide a value. This will be determined automatically.

@NewUserID: Do not provide a value. This will be determined automatically.

Identify the Report

The report is identified in the DB along with the User ID of the current owner, and stored in a temporary table. The record is queried to display the report that was found.

DROP TABLE IF EXISTS #T
SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
INTO #T
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
WHERE C.Name = @ReportName
AND S.LastStatus LIKE 'Failure sending mail: The user or group name%'

SELECT * FROM #T

Get the Old and New User ID's

The user ID for the owner account that is currently assigned to the report, and the user ID for the account that is to be the new owner are both stored in variables.

SELECT TOP 1 @OldUserID=OwnerID FROM #T
SELECT TOP 1 @NewUserID=UserID FROM Users WHERE UserName=@NewUserName

Update the Reports and Review the Results

A transaction is begun so that the changes can be applied, reviewed, and rolled back. This allows the user to see the changes that will be applied without actually keeping the changes. First, information is queried about the records that will be affected. Second, the records are updated, storing subscription ID's of affected records in the temp table #O. Last the affected records are once again queried for a "before and after" view. All reports that have the inactive owner ID are updated, since these are all potentially affected.

To permanently apply the changes, the query must be executed with "ROLLBACK TRAN" changed to "COMMIT TRAN".

BEGIN TRAN
DROP TABLE IF EXISTS #O
CREATE TABLE #O (SubscriptionID UniqueIdentifier)

SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
WHERE OwnerID=@OldUserID

UPDATE S
SET OwnerID=@NewUserID
OUTPUT deleted.SubscriptionID INTO #O
FROM Subscriptions S
WHERE OwnerID=@OldUserID
 
SELECT C.[Name], S.SubscriptionID, S.OwnerID, U.UserName
FROM Catalog C
 INNER JOIN Subscriptions S
  ON C.ItemID=S.Report_OID
 INNER JOIN Users U
  ON S.OwnerID=U.UserID
 INNER JOIN #O
  ON S.SubscriptionID=O.SubscriptionID
ROLLBACK TRAN

After executing the script all subscriptions with the invalid AD account should now be updated with the new account and, as long as permissions on the new account are correct, subscriptions should now be working again.

*If the service account for SSRS is a domain account, make sure it has permissions to read the AD account of the new owner. Otherwise subscriptions will have this error:

The permissions granted to user '' are insufficient for performing this operation

See this article for details about how to grant the required permissions: https://learn.microsoft.com/en-us/troubleshoot/sql/reporting-services/call-authzinitializecontextfromsid-api-fails