August 14, 2009 at 12:22 pm
Is it possible to change the owner of a subscription? I want to be able to create the subscription and change ownership to the one who will be maintaining it, or if someone should leave the company, I want to be able to change ownership to their replacement, etc..
Thanks
August 17, 2009 at 8:34 am
to answer my own question:
DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAIN\someuser'
SELECT @NewUserID = UserID FROM dbo.Users
WHERE UserName = 'MYDOMAINewuserhere'
UPDATE dbo.Subscriptions
SET OwnerID = @NewUserID
WHERE OwnerID = @OldUserID
September 28, 2009 at 8:28 am
Hi, adams.squared.
It's located in the ReportServer database.
Most likely you would have to use the ListChildren method to see if the new user has authorization to view the report. If yes, then you could write some code and SQL to check for the specific subscription you are looking to change. The three tables you would be looking at are Catalog, Users, and Subscriptions. You'll have to extract some data and then you can call the UpdateSubscription stored procedure if everything looks ok.
Maybe something like this (psuedocode):
Imports System
Imports System.Web.Services.Protocols
Class MySubscriptions
Private m_server as ReportServer()
Private Sub GetSubscriptionInfo()
Dim mySubId As String
Dim reportPath As String
Dim subscriptions As Subscription() = Nothing
Try
m_server.Url = "http://myserver/reportserver/reportservice.asmx"
m_server.LogonUser("OriginalOwnerOrAdmin", "Pwd", Nothing)
subscriptions = m_server.ListSubscriptions("/SampleReports/My Report", "OriginalOwnerOrAdmin")
If Not (subscriptions Is Nothing) Then
'Loop through to get your subscription here to get specific info
mySubId = subscriptions(arrayNumber).ID
reportPath = subscriptions(arrayNumber).Path
End If
ChangeSubscriptionOwner(mySubId, reportPath)
Catch
'::: Put Error Handler Here :::
End Try
End Sub
Private Sub ChangeSubscriptionOwner(ByVal id As String, ByVal path As String)
Dim userOk as Boolean
Dim reportId as String
Dim rs As New ReportServer()
Dim item As CatalogItem
Dim items As CatalogItem()
Try
'::: Set ReportServer URL and logon the user you want to be the new owner :::
m_server.Url = "http://myserver/reportserver/reportservice.asmx"
m_server.LogonUser("MyUser", "MyPwd", Nothing)
'::: ReportServer.ListChildren returns an array of CatalogItems :::
items = rs.ListChildren("ReportPath", True)
'::: See if the user has access to the report in the subscription :::
For Each item in items
If item = path Then
userOk = True
reportId = item.ID
Exit For
End If
Next
'::: If User has access then call the UpdateSubscription with the new owner :::
If userOk = True Then
'Find MyUser in Users table and get UserID
'Run UpdateSubscription stored procedure with SubscriptionId = id and OwnerID = UserId
End If
Catch
'::: Put Error Handler Here :::
End Try
End Sub
End Class
Note I haven't done this, but it looks like the only way that you'll be able to do this without deleting and re-creating. Hope this points you in the right direction.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply