Thursday, 2 February 2012

Changing SSRS subscription owner

When someone creates a subscription in reporting services, that user is given ownership of the subscription, even though they may have nothing to do with that subscription.

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!

Give in?

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!)

2 comments:

Unknown said...

It will be good if you can add the way to get a new owner id

Ravi Krishna Mamillapalli said...

the below query should give list of the report subscriptions and is OwnerID,Owner UserName and count of the Subscriptions.

Best Approach! is to have a Report Group Account and assign ownerId of the ReportGroup rather than a Specific UserID.
Create an SQL Agent job which runs daily once to check any OwnerID not matching with ReportGroupID and update as noted above in the article.

SELECT ownerid,USR.UserName AS SubscriptionOwner, count(*)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
group by USR.UserName,ownerid
order by 2 desc

Post a Comment