Reporting Subscriptions stucks in status “Pending” after domain user migration

Problem Description:

after a user’s account has been migrated from sourdeomain to targetdomain, the subscriptions created with that user account stopped working.

Resolution:

stsadm migration on SharePoint doesn’t fix this problem; you have to manually change the subscription users in the database of the SharePoint Reporting service application.

To identify:

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

To fix:

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = ‘sourcdomain\firstname.lastname’
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = ‘targetdomain\firstname.lastname’
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

(No Ratings Yet)