SQL Reporting Services Subscription Ownership

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 'MYDOMAINsomeuser' are insufficient for performing this operation., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'MYDOMAINsomeuser' 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 = 'MYDOMAINsomeuser'

SELECT @NewUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAINnewuserhere'

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.

Advertisements

NHibernate Tutorials

I have been poking around with NHibernate for a while now, but I am actually writing a small app with it at the moment.

During my time getting it up and running, I came across a few well written tutorials that I want to catalog here in case I want to return to them at some point for more in depth reading.

Great NHibernate Faq:
http://www.tobinharris.com/2007/2/3/nhibernate-faq

Fluent Interface for NHibernate:
http://blogs.hibernatingrhinos.com/…nhibernate.aspx

Alan Northam’s Tutorials:
http://devlicio.us/blogs/alan_northam/…part-i.aspx
http://devlicio.us/blogs/alan_northam/…part-ii.aspx
http://devlicio.us/blogs/alan_northam/…part-iii.aspx
http://devlicio.us/blogs/alan_northam/…part-iv.aspx