Solved

AD login name changed, SSRS subscription owner refuses to change along with it?!

Posted on 2014-04-18
5
2,745 Views
Last Modified: 2014-05-10
I've got a situation. Recently we changed our login usernames so mine went from domain\895 to domain\john (just changed name not new account). When I go to reporting services, all my subscriptions are gone from my view although they are still there. the issue is that reporting services still sees me as domain\895 when i'm now domain\john.

I can create a new subscription under domain\john but when I finish and go edit the subscription, the owner shows domain\895.

Now I can't get any subscriptions to run. They all error out with the message that user domain\895 doesn't exist or not recognized.

I don't care to transfer the ownership of existing subscriptions as there wasn't many to begin with and I can always recreate them. I just want the ownership changed over to my new login so I can create new ones. In the Users table, domain\895 exists but domain\john doesn't. I'm guessing this is a SID issue?

We're running SQL Server 2008R2 with Active Directory 2008R2. Any questions, please ask as I'm wanting to resolve this sooner than later since all users will be changing their login usernames in the near future.
0
Comment
Question by:Bryon_connexus
  • 3
  • 2
5 Comments
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 300 total points
ID: 40011689
0
 

Author Comment

by:Bryon_connexus
ID: 40017557
thanks for the suggestion, I think that is the right track. let me try it out and see if it works!
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 40017598
I'll be here

Regards Marten
0
 

Accepted Solution

by:
Bryon_connexus earned 0 total points
ID: 40042731
alright, first off, forgive the uber-late reply. yes, I've been kept super-busy by my superiors and never had the chance to get back to this until today...

so, after a lot of trial and error and taking the advice of said above, I figured out my CORE issue. the MOST IMPORTANT piece of information that should be reviewed prior to even doing anything is whether or not you have a login for sql server. I, for example, had one. THAT IS THE PROBLEM why no matter what I did with the suggestions above, I was always showing up as domain\895 and not domain\john.

i'm the db guy here so I had a login into the reporting services database as domain\895. I compared SIDs to make sure they were the same in sys.server_principals and in active directory (and they were). this led me to realize that this was my main issue. for whatever reason, sql server tagged domain\895 to my interactions inside reporting services and stuck with it even though domain\john was my new login name and that it also existed in the users table in the reportserver database behind reporting services. despite the SIDs matching up, it still didn't like it because my username wasn't matching up... odd.

so, in short, I ran the following in ssms:

alter login [domain\895] with name = [domain\john]

like magic, I could go to reporting services, create a folder, manage the folder, and it says it was created by domain\john as in before it would still retain and say it was created by domain\895. gosh, quite frustrating!

whew... now once that was accomplished, THEN you can go and reassign the subscriptions and owners and whatnot to the reporting services objects as per the advice above.

so, although Marten was fully correct, he covered the finishing half... I hope this helps others as well, especially the few individuals who are neck-deep in administrating reporting services and sql server versus the users from outside. i'm sure a regular user from the outside would have been a simple fix using the advice from Marten.

if anyone can explain why sql server behaves like this and I can verify it, I will also give the answer to that individual as well as this is the CORE issue of my question.

thanks for all the help! i'm glad I can move on!
0
 

Author Closing Comment

by:Bryon_connexus
ID: 40055656
Marten was right on the ball and correct in his response although it was not complete, it was all correct in what was advised.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Last week, our Skyport webinar on “How to secure your Active Directory” (https://www.experts-exchange.com/videos/5810/Webinar-Is-Your-Active-Directory-as-Secure-as-You-Think.html?cid=Gene_Skyport) provided 218 attendees with a step-by-step guide for…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question