none
Shared Schedules Owner change Necessary ?

    שאלה

  • Hi - I understand whenever a person who created the SSRS subscription leaves an organization the following code from http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx can be used to change the Subscription owner.

       1: DECLARE @OldUserID uniqueidentifier
       2: DECLARE @NewUserID uniqueidentifier
       3: SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
       4: SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'

    5:UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

    Now the Question:

    If the subscriptions created by 'DOMAINA\OldUser' are using Shared Schedules created by 'DOMAINA\OldUser' then do I need to update DomainA\Olduser

    with DomainA\NewUSer for CreatedbyID column in ReportServer.dbo.Schedule table ?

    Thanks in advance!


    • נערך על-ידי BIDS IDE יום חמישי 08 מרץ 2012 16:42
    יום חמישי 08 מרץ 2012 16:35

תשובות

  • Hi BIDS IDE,

    Shared schedules are created as separate items. So there is no need to change CreatebyID column.

    Lola


    Please remember to mark the replies as answers if they help.

    • סומן כתשובה על-ידי BIDS IDE יום שני 19 מרץ 2012 13:43
    יום שני 12 מרץ 2012 08:47
    מנחה דיון

כל התגובות

  • Hi BIDS IDE,

    Shared schedules are created as separate items. So there is no need to change CreatebyID column.

    Lola


    Please remember to mark the replies as answers if they help.

    • סומן כתשובה על-ידי BIDS IDE יום שני 19 מרץ 2012 13:43
    יום שני 12 מרץ 2012 08:47
    מנחה דיון
  • Thanks Lola Wang.

    Back to Part I of my initial post

    - When I asked the same question to my friends/colleagues who worked/working on SSRS said they never heard of necessity of changing the OwnerID column in ReportServer.dbo.Subscriptions table when a person leaves an organization. Now I am totally confused and would I like now what exactly happens when a subscription is triggerred. When a subscription is set a sql agent job is created on SQL Server and this is running as NT AUTHORITY\NETWORK SERVICE in our workflow and where exactly the credentials of the user who created comes into picture in the entire subscription process.

    FYI - I read this blog both 1 & 2 http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx but still I am missing that part I asked about.

    Thanks again.

    יום שני 12 מרץ 2012 16:59
  • Hi BIDS IDE,

    When the subscription runs several things happen:

    - The SQL Server Agent job fires and puts a row in the Event table in the RS catalog with the settings necessary to process the subscription

    - The RS server service has a limited number of threads (2 per CPU) that poll the Event table every few seconds looking for subscriptions to process

    - When it finds an event, it puts a row in the Notifications table and starts processing the subscription, which includes executing the data driven subscription query (if present), processing the report, rendering it to the specified format (HTML, PDF, Excel etc), and delivering the final result to either an email address or a file share. Note that there will also be a row in the ExecutionLog table for the report execution.

    As I have replied firstly, since the Shared schedules created, it will exist as an separate independent individual, which means it has nothing to do with the owner.

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.

    יום שלישי 13 מרץ 2012 08:46
    מנחה דיון
  • Thanks for your reply Lola Wang

    Actually, I tried to mimic the entire scenario by creating a test windows user account, giving content manager access to this account in SSRS and creating some subscriptions with TestUSer and another user A

    This is what I noticed when removing access permission to SSRS or disabling test users windows account(for this case the testuser permissions are not explicitly removed from SSRS)

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Report Name          SubsOwner         SharedScheduleCreator     TestUserPermissions removed SSRS              Testuser disabled

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------

    RptcreatedbyA          TestUSer                     TestUser                       Failed due to insufficient                        Ran successfully

                                                                                                             permissions

    RptcreatedbyA              A                              TestUser                           Ran successfully                                Ran successfully

    RptCreatedbyTestUser    TestUser                   TestUser                       Failed due to insufficient                        Ran successfully

                                                                                                                  permissions

    RptcreatedbyTestUser              A                    TestUser                          Ran successfully                                   Ran successfully

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Now the question is,  Looking at the results, I feel I do NOT even need to update the ownerid in dbo.Subscriptions table, is that correct ? Please confirm We are using SSRS 2008 R2 Enterprise Edition. Thanks!

            
    • נערך על-ידי BIDS IDE יום שלישי 13 מרץ 2012 20:15
    יום שלישי 13 מרץ 2012 20:13