locked
SID Mismatch RRS feed

  • Question

  • What are the resons for SID mismatch in SQL Server 2016 (AlwaysOn)?

    Recently we have migrated DB from 2008 to 2016 and i have micrated all users with SP_Revlogin.

    Everything working fine from past 1 month, but recently i found that user is not able to access DB after failover.

    When i verify the user is not mapped to DB, found that SID is Missmatch.

    What could be the reason?

    Monday, June 24, 2019 5:38 PM

Answers

All replies

  • Are you sure the user was not created on both servers, not "copied" from one to the other.  That is the most likely answer.

    Monday, June 24, 2019 5:45 PM
  • We have copied user to all secondary replicas and everything working fine.

    But suddently we got this issue from yesterday. I fixed this issue. But i am try to finding the root cause for this issue.

    Monday, June 24, 2019 5:51 PM
  • But suddently we got this issue from yesterday. I fixed this issue. But i am try to finding the root cause for this issue.

    Someone did not follow the instructions, apparently.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 24, 2019 9:36 PM
  • Hi VijayKSQL,

     

    The way to generate SIDs for different account types is different. For domain accounts, it is the domain's SID. For local Windows accounts, it is the local machine's SID. For SQL Server accounts, a SID is a self-generated one-based enumeration. Do you use scripts to transfer all login accounts to all nodes? For more details, please refer to Logins Of Applications That Use SQL Server Authentication or a Local Windows Login and

    How to transfer logins and passwords between instances of SQL Server

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 25, 2019 9:22 AM
  • These are only SQL logins not windows logins. I used below script for transfer users.

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    • Marked as answer by VijayKSQL Tuesday, July 2, 2019 8:56 PM
    Tuesday, June 25, 2019 5:11 PM