We utilize data driven subscriptions in SQL Server Reporting Services (SSRS) to automate several reports and their distribution to a group of people. For example maybe when a work order is created in the database a report with info about that order is emailed to everyone who will have to be involved in fulfiling the order.
We realized that some of these reports were not going out. By looking at the log files (located at ) it became clear that we were hitting a permissions issue:
ReportingServicesService!library!4!09/25/2008-08:15:04::
e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'MYDOMAIN\someuser' are insufficient for performing this operation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'MYDOMAIN\someuser' are insufficient for performing this operation.
Ok that seemed to make sense. The user "someuser" had left our company and so I'm sure his account was disabled. After looking around at the report, it's definition, the subscriptions, the data access, nothing was tied to this old employee.
But.... the subscription itself still is.
The downside of this is that there is no way to change who "owns" the subscription.
However, you can make the changes manually in the database with the following code:
DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAIN\someuser'
SELECT @NewUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAIN\newuserhere'
UPDATE dbo.Subscriptions
SET OwnerID = @NewUserID
WHERE OwnerID = @OldUserID
Presto, your subscription has a new owner and will once again start running correctly.
UPDATE: I am going to try to work on something that will monitor the subscriptions and notify me if one of them fail. Check back later.