How do I change the owner of a subscription in SQL Server Reporting Services

Question

The previous DBA here set up some SQL Server Reporting Services Reports to run automatically and email the report to users. When he left his accaount was disabled and now they don't work. The status on the subscription reads:

Failure sending mail: The permissions granted to user 'OURDOMAIN\old_DBA_Username' are insufficient for performing this operation.

Is there an easy way to change the owner, I have found the Owner field in the RS database in the subscriptions table and have though of just changing that, but don't want to break our production report service?

The other option of course is to create a new subscription and delete the old, but surly there is a better way.

Lamellicorn

Answer 1

The solution posted here did the trick for me. Basically you midify the subscription owner on the SSRS database directly by running the script below.

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

Azurite

Technically, this works fine (up through SSRS 2019), but you should be using the UpdateSubscription sproc instead (or at least double-checking what it does), just in case some future change to SSRS introduces additional dependencies. – Flaxen

Answer 2

You can do this in Visual Studio in C# as well. Create a web reference to the ReportService2010.asmx on your report server, the along the lines of this.

public class ChangeOwner
{
public string OldOwner { get; set; }
public string NewOwner { get; set; }

public ChangeOwner()
{
}

public void ChangeReportOwner()
{
    ReportingService2010 rs = new ReportingService2010();
    //set the URL to your report server
    rs.Url = "http://youserver/ReportServer/ReportService2010.asmx";
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

    Subscription[] items = rs.ListSubscriptions("/");

    foreach(Subscription item in items)
    {
        if(item.Owner.ToUpper() == this.OldOwner.ToUpper())
        {
            Console.WriteLine("Updating report " + item.Path + " " + item.Owner);
            rs.ChangeSubscriptionOwner(item.SubscriptionID, this.NewOwner);
        }
    }
}

}

Elvinaelvira

This is actually the preferred way to do this, as modifying the SQL tables of SSRS directly as recommended by others is not supported - although effective. – Cartercarteret

FYI, the SOAP/asmx interface uses the UpdateSubscription sproc under the hood. – Flaxen

Answer 3

I ran into this issue before and found the answer in this article on Jeremiah Clark's blog.

Gist of it is, you're right on about updating the Owner field in the Subscriptions table of the ReportServer database with the new user.

Worked for me, anyway.

Wilful

If this actually contained the answer rather than just a link I would upvote it. – Cartercarteret

Answer 4

From SQL 2016 onwards, there is a feature in the user interface to replace a subscription owner. The only down side to this is that you have to edit each subscription individually. Not a problem if you only have 1, but if you have dozens you'll want to use one of the other suggestions instead.

Source: https://www.sqlshack.com/report-subscription-changes-sql-server-reporting-services-2016/

Venetic

Answer 5

Starting with SRSS 2008 R2, you can programmatically change the owner via the ChangeSubscriptionOwner method of the ReportingService2010 web service.

You can call this web service via an .rss script. eg.

' OldOwner - username of owner to replace
' NewOwner - username of new owner

Public Sub Main()
    Dim items() As Subscription

    items = rs.ListSubscriptions("/")

    Dim item as Subscription
    For Each item in Items
        If (item.Owner.ToUpper() = OldOwner.ToUpper()) Then
            Console.WriteLine("Updating report " & item.Path & " " & item.Owner)
            rs.ChangeSubscriptionOwner(item.SubscriptionID, newOwner)
        End If
    Next item
End Sub 

And run this using the rs.exe tool like this:

rs.exe -i ChangeSubscriptionOwner.rss -s http://localhost/reportserver -e Mgmt2010 -v NewOwner="domain\newuser" -v OldOwner="domain\olduser"

Coster

Answer 6

Can't you modify the subscription?

http://technet.microsoft.com/en-us/library/ms157343.aspx

Myrmecology

I can make changes to the subscriptions but that does not seem to update the owner field. – Lamellicorn

Answer 7

I had to face the same problem and used reflector to find what the issue was. I have shown the .Net code within ReportingServicesServer.dll which throws this exception in the post below.

http://easybi.wordpress.com/2011/01/22/microsoft-reportingservices-diagnostics-utilities-unknownusernameexception-the-user-or-group-name-is-not-recognized/

As briang suggests above, the solution provided by Jerimiah Clark works. My post details what happens behind the scenes.

Cosimo

Answer 8

You can use the attached stored procedure: You need to provide the new user you want to assign the subscription to and the report name that is subscribed

CREATE  PROCEDURE [dbo].[SP_ChangeSubscriptionOwner] 
(
@userName nvarchar(260),
@ReportName nvarchar(425)
)

/*
  Example: 
  EXEC SP_ChangeSubscriptionOwner '<New user Name>' , '<Report Name>'

   The procedure changes the Owner of a subscription to the user enterd as  parameter to the procedure

*/

AS 
BEGIN
SET NOCOUNT ON

DECLARE @MSG VARCHAR(1000)
IF NOT EXISTS 
 (
   SELECT 1 
       FROM Users where UserName = @userName
 )

   SET @MSG = 'User: '+'"'+  @userName + '"'+' doesn''t exist in Users table.'
   ELSE 
   SET @MSG = 'No subscriptions were found to report: '+'"' + @ReportName + '"'+' ,Or the User: '+'"'+  @userName + '"'+' is already the owner of the report subscription.'



 update S 
 set S.OwnerID = U.UserID
 FROM Subscriptions S
 JOIN Catalog c
 on s.Report_OID = c.ItemID
 JOIN Users U 
 ON U.UserName = @userName
 where C.Name = @ReportName
 AND U.UserID != S.OwnerID



  if @@ROWCOUNT = 0 
   raiserror (@MSG,16,1)

  END

Hamitic

Answer 9

ntombela's solution worked for us on SSRS 2016, except that we also had to update ModifiedByID. If we didn't, we were not able to disable/enable subscriptions. We got a pop-up indicating that the now-disabled ID wasn't found. The owner showed the correct username, but the Modified By entry still showed the old ID.

I wouldn't have thought it mattered, but it seemed to for us. On the up side, we stopped seeing the SSRS log file errors like:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: , Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'AD Domain\olddisabledusername' is not recognized.

Frizette