If you work with replication much I’m sure you’ve come across the situation whereby somebody created a publication and at some point in the future left the company. Once their AD account had been disabled or removed then the replication jobs, for some reason, would stop functioning and you would have to spend your time trying to figure out why. Eventually you would realize that the jobs had been mysteriously created under the context of that user account and so now longer work. A quick change so that jobs run under SA and everything is all happy happy again.

Well I had something similar happen this week with Reporting Services where all of a sudden email subscriptions were failing and critical reports were not being sent out. This is not the kind of call that you want to get during the night.

Identifying the failure was pretty easy, that was just a case of opening up the SSRS log file (by default found at C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesLogFiles) and looking through for the error:

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation. —> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ‘ <domain><user> ‘ are insufficient for performing this operation.Mail will not be resent.

Error in hand I went looking at the report in question. It had been created by the user who had left the company and so had two subscriptions. The first subscription was file based. That was having no trouble executing, which was interesting. The other, an email subscription was the one that was failiing. 

My belief at this juncture was that the file subscription, which has a user context for execution, was working ok because of that user context and the other was failing because the AD account of the old user was no longer around.

Going back on old knowledge I figured that I could just change the owner of the subscription and everything would be happy again and the reports would flow. I spent a good 30 minutes trying to figure out a way to do that. There’s actually no option, you have to drop and recreate the subscription. For me this would require changes to a lot of subscriptions, with several of them being data driven. Doing this would have taken an age.

Time to see if a meta-data update would help fix the issue.

I started digging through the ReportServer database and was able to come up with a list of subscriptions that were owned by the old user:

SELECT  u.UserName ,

        s.SubscriptionID ,

        Report_OID ,

        Description ,

        LastStatus ,

        EventType ,

        DeliveryExtension

FROM    ReportServer.dbo.Subscriptions s

        INNER JOIN ReportServer.dbo.Users u ON s.OwnerID = u.UserID

        WHERE u.UserName = '<old user>'

With this information in hand I went out and create a new AD account specifically for owning reports that would send emails. After making sure that the account had permissions to access the reports that it would subscribe to I went and updated the subscriptions so that they would run under this new security context:

BEGIN TRAN

BEGIN TRY

    DECLARE @newid UNIQUEIDENTIFIER 

    SELECT  @newid = userid

    FROM    ReportServer.dbo.Users

    WHERE   UserName = '<new user>'

    UPDATE  s

    SET     OwnerId = @newid

    FROM    ReportServer.dbo.Subscriptions s

            INNER JOIN dbo.Users u ON s.OwnerID = u.UserID

    WHERE   u.UserName = '<old user>'

END TRY

BEGIN CATCH    

    SELECT  ERROR_MESSAGE()

    IF @@TRANCOUNT > 0 

        ROLLBACK TRAN;

END CATCH

IF @@TRANCOUNT > 0 

    COMMIT TRAN;

This updated 73 reports for me and only took a couple of seconds. Much better than the couple of days it would have taken to try and change every subscription.

Knowing this I’ll be keeping a much close eye on subscription ownership in SSRS going forward.