Tip: Change the Owner of SQL Reporting Services Subscription


Here is the scenario:

Your organization has a SQL Reporting Server that generates custom reports based on some of your data.  These reports need to be generated each day at noon and then emailed to someone important.  So Tom, your current DBA, creates subscriptions for the reports so that they are automatically sent to Mr/Mrs Important. All is well in the world.

Now fast forward six months:

Tom, your DBA, has decided to leave your organization.  Like all organizations should do, Toms account is deleted from Active Directory.  A few days later, you start getting calls from Mr/Mrs Important wondering why they have not received the emailed reports for the last two days.

It turns out that Tom created the subscriptions using his account, so he is the Owner of the subscriptions.  When you look at the history for the subscriptions, you get an error that the subscription owner "DomainA\Tom" does not exist.  Now you need to find a way to fix this before Mr/Mrs Important sends you looking for a new job.

The Problem

OK, so this really happened on a project that I am working on, although the above scenario is made up because the real story is quite lame.  The problem is the same though: I needed to change to Owner of the subscription to an existing account. 

My first thought was that surely the Report Manager or Management Studio would provide a way to accomplish this.  Wrong assumption on my part...there is absolutely no way to do this using the designer tools that interface with SQL Reporting Services.  That left me with two options:  recreate the subscriptions using a new account (not really efficient), or find a way to change the Owner.

The Solution

It turns out that you can modify the Owner in the Subscriptions table in the ReportServer database in SQL.  This table contains a column called OwnerID that is a foreign key reference to the UserID column in the Users table of the same database.  To fix the problem, just replace the old OwnerID with the new one from the Users table that matches the new user that you want to use. 

Here is a simple SQL statement that will do the replacement (where OldUser and NewUser are the users that you are trying to swap):

   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

There could be one final catch: the new user might not exist in the Users table.  If that is the case, then you need to add/modify/delete something in the Reporting Server using that account and then it will appear in that table.  All I did in this case was a file in the Report Manager and then delete it.
Comments (34)

  1. Hans says:

    Thanks, that worked for me.   Now my question is: what’s the easiest way to ensure that this problem never arises again?  I want all my subscriptions to be owned by built-inadministrators or some other account that will never go away.  

  2. Nathan says:

    @Hans – well you could create a domain account for subscriptions and then make sure subscriptions are only created when logged on as that account. A bit of a pain but would work.

    Or you could script something based on the above solution to run periodically and update any owners that are not the "subscription owner" domain account you created (making sure that this account is in the users table first, as described above).

  3. Alex says:

    built-inadministrators is a bad idea.  it’s not something that will always go away.

    you’ll get a DBA that’ll whack that so fast cause it’s a security hole.

  4. phoeneous says:

    Or you could just do this 🙂

    DECLARE @NewUserID uniqueidentifier

    SELECT @NewUserID = UserID FROM dbo.Users

    WHERE UserName = ‘DOMAINNewUser’

    UPDATE dbo.Subscriptions SET OwnerID = @NewUserID

  5. Kotti says:

    Thx guys,

    very helpful…..

    Have a nice day

    Kotti

  6. Kevin says:

    This helped me a lot!

    Thank you for this quick solution!

  7. Sachin says:

    Thank you very much!. This article helped me.

  8. Mike says:

    Thanks for the help.  Saved me a ton of time.

  9. Delly says:

    Cheers guys huge help! It would seem MS need to make this a little easier to manage, but we have implemented Nathan's suggestion.

  10. ajflores.dev says:

    Thanks! Worked perfectly …

  11. Jegan says:

    Thanks for the tip. I had to face the same problem and had to use reflector to find what the issue is. I have detailed the DB calls involved in the ReportingServicesServer.dll in the post here.

    easybi.wordpress.com/…/microsoft-reportingservices-diagnostics-utilities-unknownusernameexception-the-user-or-group-name-is-not-recognized

  12. Warehouse Link says:

    Thanks!  Saved me from recreating 37 separate subscriptions!  A++++ would take your advice again 😉

  13. Anna says:

    I have the same issue, the problem is I cannot  find my own username in dbo,user table.

    I tried all the way, to add a file, to upload something, my account still not shown in users table.

    What can I do

  14. Sorry, add to my above post,  I ends up to delete those subsciption and create them by logging using my account. Then in the GUI, right click a subscription and I can see the owner is me.

    But when I query the tables by joining subscription table and user table on ownid=userid, the username is the former person's login,  This is a real mystery for me, so where and which table I can find my login name?

  15. TomVdP says:

    Thank you.  Exactly the problem we faced.

  16. Ashwin says:

    Jeremiah,

    That was an excellent post. It solved my problem the minute I updated the owner. Thanks a lot.

  17. Mahesh S says:

    I'm able to update the OwnID in the subscription table but when i click on subscription of tab of report in report server,its throwing error of  " an internal error occured on the report server". When i changed back it again & set the OwnerID same as i had previously then its works fine. Let me know whether this change appicable to Data driven subscription.? or i have to update anything else.

  18. ank says:

    Great post! Effective, concise and funny at the same time!

  19. Jason says:

    Don't ever do this!  You should be using the object model and NEVER updating data directly in SQL tables.

  20. Gurpreet Singh says:

    Hope we all understand that the above method would cause the reporting services instance to be unsupported.

    Jeremiah : Could you please update your blog to mention the supportability of the above solution?

  21. Lwazi Radebe says:

    Thank you very much, this was very helpful

  22. Ram V says:

    Thanks for the quick tip which came in handy.

    Thanks,

    Ram

  23. MJ Hufford says:

    Great tip – very handy!  Thanks!

  24. nitish Kumar says:

    This is really helpful

    Thanks a lot

  25. Dune says:

    Thank you, you saved me a lot of time

  26. Ned says:

    This is terrific. Thank you so much. You have saved us loads of time.

  27. Donna says:

    Thank you very much. This is very helpful to us. 🙂

  28. Jim says:

    Can't a reporting administrator see all subscriptions and edit them? I see a note on that on another web page but I don't see them even if I am made an admin.

    Thanks!

  29. Jim says:

    The reason I ask is I have two users who need to work on the subscriptions. I could change them back and fourth on the ownership but both are admins and it would be great if they could both just edit them.

    Thanks!

  30. Tomas says:

    I freaking love you man. Been fighting this issue for days…

  31. Vu says:

    I have a lot of subscriptions are pending for couple days or weeks.  Do anyone know how to fix this issue?  I google but so far no luck.

    Thanks,

    Vu

Skip to main content