none
MSSQL 2012 Always on cluster - Sid mismatch but still able to login

    Pertanyaan

  • Hi

    I am under the understanding that syslogins and sysusers are linked via SID, when sid mismatch happens the login cannot be used to access the database. 

    The above is true in my primary the sid matches and i was able to login as well, but on my secondary replica the SID are not the same but still i am able to login, cant figure out the reason why, hope anyone can shed some light.

    I logged in with the actual VM name instead of the AG listener name to make sure i am connecting to the primary or the secondary replicas. 

    USE [DB_NAME]

    EXEC SP_CHANGE_USERS_LOGIN 'report'; 

     I executed the above command on primary and secondary replica and it did report the user that is orphaned with sid mismatch on secondary and not on primary. but i can login to both primary and secondary with no problems.


    -Dhayanandh

    03 Mei 2018 1:27

Semua Balasan

  • Who did you login as? Apparently that login has to exist on the secondary! Does thhat login has a user in the database (with the proper SID)? Or perhaps your login is sysadmin?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    03 Mei 2018 10:56
  • Hello

    When creating SQL Authenticated Logins for an Availability Group, you essentially need to do the following: -

    Create it on the Active Node

    do a SELECT * FROM syslogins to get the sid

    Recreate the Login on the other instances, specifying the WITH sid option

    DROP LOGIN TestLogin; 
    GO 

    CREATE LOGIN TestLogin  
    WITH PASSWORD = 'SuperSecret52&&', SID = 0x241C11948AEEB749B0D22646DB1A19F2; 

    SELECT * FROM sys.sql_logins WHERE name = 'TestLogin'; 
    GO


    Please click "Mark As Answer" if my post helped. Tony C.

    03 Mei 2018 11:09
  •  I executed the above command on primary and secondary replica and it did

    report the user that is orphaned with sid mismatch on secondary and not on primary. but i can login to both primary and secondary with no problems.

    Were you able to log in and access the secondary replica? Is it a readonnly secondary?

    Maybe your login is sysadmin? In that case, you always access the database as dbo.

    In any case, you use use "SELECT USER" to see which user you map to in the database.

    03 Mei 2018 21:48
  • Hi, Thanks for your response i logged in with the user that has the sid mismatched.

    i used SSMS on my secondary replica server selected the DB user login, entered the sid mismatched user, clicked login and it logged me in successfully.

    This is where i am confused.


    -Dhayanandh

    04 Mei 2018 1:28
  • If you show us the result from below, we might be able to tell something:

    SELECT ORIGINAL_LOGIN(), SUSER_SNAME(), USER_NAME(), IS_SRVROLEMEMBER('sysadmin')


    Tibor Karaszi, SQL Server MVP (Web Blog)

    04 Mei 2018 10:00
  • i used SSMS on my secondary replica server selected the DB user login, entered the sid mismatched user, clicked login and it logged me in successfully.

    That does not sound very strange to me. That would only fail if the login has the database which is the secondary replica as the default database, or you specified that database on login.

    If the default database is master, tempdb or something else, there is nothing that blocks you from logging in.

    04 Mei 2018 21:35