Re: SQL Server Question

Thanks, Andy- that was where I was going to go, too. The login names and db users may look the same, but the unique identifiers that SQL Server Always-on AG identifies them with see them as completely different.

Have a great day and it looks like you have your solution, Scott!

*Kellyn Gorman*
DBAKevlar Blog <http://dbakevlar.com>
about.me/dbakevlar

On Wed, Sep 13, 2023 at 11:45 AM Scott Canaan <srcdco_at_rit.edu> wrote:

> Thank you everyone. That is exactly what happened and I have a plan in
> place to fix it tomorrow morning when it fails back.
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> *From:* Andy Sayer <andysayer_at_gmail.com>
> *Sent:* Wednesday, September 13, 2023 2:38 PM
> *To:* dbakevlar_at_gmail.com
> *Cc:* oracle-l_at_freelists.org; Scott Canaan <srcdco_at_rit.edu>
> *Subject:* Re: SQL Server Question
>
>
>
> Hi Scott,
>
>
>
> The SIDs of the server principals (logins) are probably different between
> your AO sites. I suggest grabbing the SIDs from your current primary site,
> then recreate the logins in your secondary site including the SID= clause.
>
>
>
> When you create new logins, you can either grab their SID on the primary
> before you create on secondary. Or you could just hardcode a SID in advance
> and create the exact same login on all instances.
>
>
>
> Hope that helps,
>
> Andy
>
>
>
>
>
> On Wed, Sep 13, 2023 at 10:54 AM, Kellyn Pot'Vin-Gorman <
> dbakevlar_at_gmail.com> wrote:
>
> Hey Scott,
>
> Are you saying you have to run sp_change_user_login each time a failover
> occurs to sync all the db users with the server logins? I just want to
> ensure I understand the exact challenge you’re facing…
>
>
>
> Thanks
>
> Kellyn
>
>
>
> On Wed, Sep 13, 2023 at 09:55 Scott Canaan <srcdco_at_rit.edu> wrote:
>
> I know this is not normal, but I don’t have any other place to ask:
>
>
>
> We have a SQL Server AlwaysOn cluster. Whenever a failover occurs, the
> database accounts become orphaned. I can fix it manually once it fails
> over. Everytime it fails over, the login becomes orphaned again.
>
>
>
> Does anyone know how to fix this?
>
>
>
>
> *Scott Canaan ‘88 *
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l