SSRS Subscription Failure sending mail: The user or group name ‘domain\user’ is not recognized.Mail will not be resent.

When you’re getting the error above is because the user is no longer valid for Reporting Services.
Quick Fix –> Re-create the subscription.
Best Fix –> Reassign the subscriptions to a Service Account.

However, if you have several subscriptions it is not feasible to recreate all of them. Here it is how can you reassign these subscription to someone else.

1. Create a Service Account to assign it as owner of these subscriptions. This is because the Active Directory account set before is no longer valid (the employee left the company.) If you just use another user account and that user left the company you’ll have to do it over and over again. Account created: SA_REPORTS_P

2. Grant DBO permissions on ReportServer database to the new Service Account (SA_REPORTS_P)

3. Grant ALL permissions to the Service Account on Report Server Home directory. Make sure all the folder inherit permissions from parent, if not, individually check the permissions in each folder.

SSRS Permissions grant
SSRS Permissions grant

4. Find the OWNERID of the subcriptions that have an invalid user or group name. Run the query below in your ReportServer database and find those with the error.

--Query to get the Last Status of each Subcription and its OwenerID.
SELECT s.[SubscriptionID] -- Subscription ID
,s.[OwnerID] -- Report Owner
--,s.[Report_OID] -- Report ID
, c.Path -- Report Path
--,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server
,s.[LastStatus] -- Status of last subscription execution.
,s.[Description] -- Description of the report subscription
,s.[EventType] -- Subscription type
,s.[LastRunTime] -- Last time subscription executed
--,s.[Parameters] -- Parameters used for subscription
,s.[DeliveryExtension] -- How to deliver the subscription
FROM [ReportServer].[dbo].[Subscriptions] as s left join dbo.Catalog as c
on c.ItemID = s.Report_OID left join dbo.ReportSchedule as rs
on rs.ReportID = s.Report_OID
order by s.LastStatus

OwnerID_LastStatus

5. Identify OwnerID for the Service Account Created (or whatever account you’re going to use). Run the query below to list the users.

SELECT [UserID],[UserName]
FROM ReportServer.dbo.Users

SSRS_users
6. Replace the invalid OwnerID with a valid OwnerID. I use the Service Account ID.

UPDATE [Subscriptions]
SET [OwnerID] = '96C17DCA-42D6-44AE-B036-9EA1F870AEFD' -- New VALID OwnerID
WHERE [OwnerID] = 'EFBCD353-3745-41E4-B2BF-8C23198014AD' -- Old and invalid OwnerID

7. Verify the subscriptions were updated. Run the query below to see how the new user name is not in place.

--Query to get the Report Name and its Subcription Owner.
SELECT
jobs.name AS JobName,
C.path AS ReportPath,
C.name AS ReportName,
u.username AS SubscriptionOwner
FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs
ON CONVERT(varchar(36), RS.ScheduleID) = jobs.name
INNER JOIN dbo.Subscriptions S
ON RS.SubscriptionID = S.SubscriptionID
INNER JOIN dbo.Catalog C
ON s.report_oid = C.itemid
INNER JOIN dbo.users u
ON s.ownerid = u.userid
order by u.username

SSRS_SubscriptionOwner

The problem should be fixed at this point. Nevertheless, if you want to check if it really works, you can wait for the next subscription to run or invoke it manually. So, here you have a bonus on how to manually call a subscription.

A. In ReportServer, execute the query below and it will give you the SQL Job Name. Two Jobs (subcriptions) that were failing due to an invalid OwnerId are 36F500A6-58DA-4ACB-87AF-9947FEBB5797 and
542C8818-914D-4BAF-A9AA-C58FCADC24E0

--Query to get the SQL Job Name of each subscription in the server
SELECT
jobs.name AS JobName,
C.path AS ReportPath,
C.name AS ReportName,
u.username AS SubscriptionOwner,
s.LastStatus
FROM dbo.ReportSchedule RS JOIN msdb.dbo.sysjobs jobs
ON CONVERT(varchar(36), RS.ScheduleID) = jobs.name
	INNER JOIN dbo.Subscriptions S
ON RS.SubscriptionID = S.SubscriptionID
	INNER JOIN dbo.Catalog C
ON s.report_oid = C.itemid
	INNER JOIN dbo.users u
ON s.ownerid = u.userid
order by s.LastStatus

SSRS_SQLJobName

B. Execute the job manually with the script below.

EXEC msdb.dbo.sp_start_job @job_name = '36F500A6-58DA-4ACB-87AF-9947FEBB5797'

C. Validate the new status by executing the script in step A.
SSRS_SQLJobName_result2.jpg

6 thoughts on “SSRS Subscription Failure sending mail: The user or group name ‘domain\user’ is not recognized.Mail will not be resent.

  1. Awsome! We had the same issue happen with our report subscriptions. Thank you for posting this.

  2. Great post, shouldn’t happen more than once. I know I will definitely use the last bit to re-run on last status in the future. Thanks a bunch!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s