Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-18
5
Medium Priority
?
3,301 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 Huffman
  • 3
  • 2
5 Comments
 
LVL 20

Assisted Solution

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

Author Comment

by:Bryon Huffman
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 Huffman 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 Huffman
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

971 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