It is that time again. This time coming to you from a Mcdonald’s so my son can get out some energy, and it is 105 degrees outside.
This will solve what I consider a bug in how SSRS creates subscriptions.
Problem
Recently, I was tasked with creating an email subscription to a new SSRS report in an environment that I was not familiar with. I have created my fair share of subscriptions in my day, and this one was very straightforward.
I found the report, clicked on Manage, and went to the Subscription page. Clicked on New Subscription and filled in all the information, easy peasy.
The subscription is ready to go when the schedule kicks in the next day, or so I thought.
I did not try and send the email to the user listed in the failure results. Why do we care about the user that created the subscription?
Resolution
After researching and digging through the SSRS logs, I found that when you create a new subscription, the record is stored in the ReportServer databases Subscription table with the login account that created the subscription. This is pertinent to troubleshooting because if the account does not have an email address associated with it or does not have permission to send an email, your subscription will fail.
Then I decided to run a query on the Subscription table and joined the Users table to get the user name.
This report subscription shows TheUser has the account used to create the email subscription. And as we saw from the error and logs, this user does not have email permissions. We need to update that value in the Subscription table to a valid account in the Users table to fix the problem.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE ReportServer GO SELECT s.SubscriptionID, u.UserName AS TheUser, s.ModifiedDate, s.LastRunTime, s.[Description] ,c.[Name] AS TheReport FROM [Catalog] c INNER JOIN Subscriptions s ON s.Report_OID = c.ItemID INNER JOIN Users u ON u.UserID = s.OwnerID ORDER BY u.UserName /* -- Update OwnerID for existing subscriptions DECLARE @OldUserID uniqueidentifier DECLARE @NewUserID uniqueidentifier SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'Domain\adm' SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'Domain\emailaccount' UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID AND SubscriptionID = '3E42B6FD-9B21-4977-AE63-82D5C86D344F' */ |
This query will show you all the subscriptions with the owner account listed. Then, an update statement is listed that will change the account from what was used to create the subscription to an account with proper permissions.
