This can cause a problem if that person leaves and their account is disabled, as the recipients of the subscription stop receiving their e-mail report, despite the SQL agent job completing successfully. Looking at the subscription details of the relevant reports, you may see a message that:
|Failure sending mail: The permissions granted to user 'Grumpydomain\Old.DBA' are insufficient for performing this operation.Mail will not be resent.|
To fix this, we need to change the owner of the subscription. Easy, you think - let's just select "change owner" on the Subscriptions tab and ... hmm ... well let's just edit the subscription and over-type the owner field ... ahh ... OK, well it must be in the reporting services config app under the email settings ... Rats!
Well, there are probably other ways of doing it, but I found the easiest fix was simply to change the data directly in the ReportServer database.
The table you need to change is Subscriptions, wherever the OwnerID value matches the relevant UserID field from the Users table.
Once you've identified an alternate UserID to use, just update the Subscriptions table
|UPDATE dbo.Subscriptions |
SET OwnerID = '12345678-ABCD-1234-5678-0123456789AB' -- new owner ID
WHERE OwnerID = 'ABCDEFGH-4321-WXYZ-9876-ABCDEFGHIJKL' -- old owner ID
You should now find the subscription runs (see this other post for working out which job you need to run though to test this!)