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

Rate

4.33 (3)

Share

Share

Rate

4.33 (3)