We have a number SCCM Reporting Services subscriptions for our automated reporting. Yesterday I had a former team member’s ID removed from SCCM access and today most of the automated reports did not run.
When a report subscription is created the owner is the logged on user. If that user ever loses access to SCCM/SSRS then the subscription will fail to run. I decided the best course of action was to reassign ALL subscriptions to the SCCM service account. That won’t prevent problems with new subscriptions, but it will prevent the problem with existing ones. I could run the t-SQL on a schedule in SQL itself to handle that. The only downside is that there will be no results when clicking the “My Subscriptions” link.
Thanks to Jeremiah Clark for the explanation and some t-SQL: http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx
[cc lang=’sql’ line_numbers=’false’]
USE ReportServer
Declare @NewUserName [nvarchar](260); Set @NewUserName = ‘NewDomain\NewUser’
Declare @NewUserID [uniqueidentifier]
— Show Subscriptions for a User
select U.UserName [OwnerUserName], S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = @NewUserName
— Show UserID for a User
select @NewUserID=U.UserID From Users AS U where U.UserName = @NewUserName
Select @NewUserID [NewUserID], @NewUserName [NewUserName]
— Show records for Reassigning the User (Owner)
Select @NewUserID [NewUserID], U.UserID, U.UserName, S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName
— Reassign the User (Owner)
Update S Set S.OwnerID = @NewUserID From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName
[/cc]
Enjoy