如何在SQL Server Reporting Services中更改订阅的所有者

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:

此前的DBA设置了一些SQL Server Reporting Services报告以自动运行并通过电子邮件将报告发送给用户。当他离开他的帐户时被禁用,现在他们不工作。订阅状态如下:

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

发送邮件失败:授予用户'OURDOMAIN \ old_DBA_Username'的权限不足以执行此操作。

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?

有没有一种简单的方法可以更改所有者,我在订阅表中的RS数据库中找到了所有者字段,虽然只是更改了它,但又不想破坏我们的生产报告服务?

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

另一种选择当然是创建一个新的订阅并删除旧的,但是有一种更好的方法。

7 个解决方案

#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.

这里发布的解决方案对我有用。基本上,您可以通过运行下面的脚本直接在SSRS数据库中调整订阅所有者。

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

#2

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

我之前遇到过这个问题,并在Jeremiah Clark的博客上找到了这篇文章的答案。

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.

要点是,您正在使用新用户更新ReportServer数据库的Subscriptions表中的Owner字段。

Worked for me, anyway.

无论如何,为我工作。

#3

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.

您也可以在C#中的Visual Studio中执行此操作。在报表服务器上创建ReportService2010.asmx的Web引用,与此一致。

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);
        }
    }
}

}

#4

Can't you modify the subscription?

你不能修改订阅吗?

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

#5

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.

我不得不面对同样的问题,并使用反射器来找出问题所在。我在ReportingServicesServer.dll中显示了.Net代码,它会在下面的帖子中抛出此异常。

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.

正如上面的briang所说,Jerimiah Clark提供的解决方案有效。我的帖子详细介绍了幕后发生的事情。

#6

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

从SRSS 2008 R2开始,您可以通过ReportingService2010 Web服务的ChangeSubscriptionOwner方法以编程方式更改所有者。

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

您可以通过.rss脚本调用此Web服务。例如。

' 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工具运行它:

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

#7

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

#1