Fix SSRS Subscription Owners
I recently did some work at a shop where many SSRS reports failed one Monday morning after a former employee's domain account was removed.
After frantically re-creating subscriptions to get the users reports, I realized that all of my subscriptions would again fail when my domain login was removed.
I created the enclosed stored procedure and scheduled a weekly job to make sure the subscription owners are valid.
Modify the enclosed code with your Instance, Domain, and other information appropriate for your shop.
-- usp_FixSubscriptionOwners.sql
--
-- Tue 02/28/2012 11:15:11
-- Michael Lascuola
--
-- Stored procedure to update SSRS Subscription owners to avoid
-- "Failure sending mail: An error has occurred during report processing"
-- SSRS errors.
--
-- Required is a valid login, preferrably not associated with any user.
-- Replace 'MyNTDomain\MyServiceAccount' below with this account.
-- Connecting once to SSRS while logged in with this account will create an SSRS User record.
--
-- Schedule this job weekly or whenever will be needed to alleviate failure of subscriptions
-- associated with invalid domain logins.
--
:connect MyInstanceName
USE ReportServer
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'usp_FixSubscriptionOwners') AND type = 'P')
DROP PROCEDURE usp_FixSubscriptionOwners
GO
CREATE PROCEDURE usp_FixSubscriptionOwners
AS
SET NOCOUNT ON
DECLARE @tblUsers table (UserName SYSNAME, IsValid bit)
DECLARE @strSQLCommand VARCHAR(MAX)
,@strThisUser SYSNAME
,@strDomainName VARCHAR(256)
-- Populate @strDomainName with My domain name
SET @strDomainName = 'MyNTDomain'
-- Populate temp table with complete list of ReportServer users
INSERT INTO @tblUsers (UserName, IsValid)
SELECT UserName, 1 FROM dbo.Users
WHERE UserName LIKE @strDomainName + '\%'
WHILE EXISTS (SELECT 1 FROM @tblUsers WHERE IsValid = 1)
BEGIN
BEGIN TRY
SELECT TOP 1 @strThisUser = UserName FROM @tblUsers
SET @strSQLCommand = 'exec master.dbo.xp_logininfo ''' + @strThisUser + ''';'
PRINT @strSQLCommand
EXEC (@strSQLCommand)
END TRY
BEGIN CATCH
-- Set IsValid flag to 0 if login not longer on domain
IF (ERROR_NUMBER() = 15404) -- "Could not obtain information about Windows NT group/user"
UPDATE @tblUsers SET IsValid = 0 WHERE UserName = @strThisUser
ELSE
BEGIN
DELETE FROM @tblUsers WHERE UserName = @strThisUser
-- Log unexpected error
INSERT INTO msdb.dbo.JobErrorLog
(SourceProgram, SourceLine, ErrorNumber, ErrorMessage)
VALUES
(ERROR_PROCEDURE(), 'Line: ' + CONVERT(varchar, ERROR_LINE()),
ERROR_NUMBER(), ERROR_MESSAGE());
END
END CATCH
DELETE FROM @tblUsers WHERE UserName = @strThisUser
END -- End WHILE loop
-- Now @tblUsers will be populated only with invalid logins
IF EXISTS (SELECT 1 FROM @tblUsers)
BEGIN
DECLARE @NewUserID uniqueidentifier
SELECT @NewUserID = UserID FROM dbo.Users WHERE
UserName = 'MyNTDomain\MyServiceAccount' -- Replace with account that will not be removed
IF @NewUserID IS NOT NULL
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID
WHERE OwnerID IN (
SELECT UserID FROM dbo.Users
WHERE UserName IN (SELECT UserName FROM @tblUsers))
SELECT @@rowcount AS 'RowsChanged';
END
-- Send email if records added to ErrorLog
IF EXISTS (SELECT 1 FROM msdb.dbo.JobErrorLog WHERE DATEDIFF(dd, Stamp, GETDATE()) < 1)
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<font name = "Verdana"><B>BoA Load Error</B>' +
N'<table border="1">' +
N'<tr><th>Stamp</th><th>Error Message</th>' +
N'</tr>' +
CAST ( ( SELECT td = CONVERT(CHAR(20), Stamp, 120), '',
td = ErrorMessage, ''
FROM msdb.dbo.JobErrorLog
WHERE DATEDIFF(dd, Stamp, GETDATE()) < 1
ORDER BY Stamp
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
exec msdb.dbo.sp_send_dbmail
@recipients = 'MyEmail@MyDomain.com' -- Replace with your notification email address
,@subject = 'usp_FixSubscriptionOwners Error'
,@body = @tableHTML
,@body_format = 'HTML'
,@attach_query_result_as_file = 0
,@profile_name = 'MyEmailProfile' -- Replace with your SQL mail profile
END
GO 
