SQL SERVER – Create Login with SID – Way to Synchronize Logins on Secondary Server

How many times you had a problem where you have deployed a high availability solution, but the application is not able to use database and getting login failed for user after failover? Note that this issue would happen only with SQL Logins not Windows Login. You would understand the reason shortly. Here are the situations where you might face login failure.

  1. Log Shipping (reading from stand-by secondary database)
  2. AlwaysOn Availability Group. (reading from read-only secondary replica)
  3. Database Mirroring (after failover)

Let’s assume that we have SQLServerA and SQLSevrerB which has one database AppDB. As per architecture of the users and login in SQL Server – there would be a user in database mapped to login. For simplicity let’s say login and user is AppUser which is available in AppDB.

USE AppDB GO SELECT name, sid FROM sys.sysusers WHERE name = 'AppUser' GO USE MASTER GO SELECT name, sid FROM sys.sql_logins WHERE name = 'AppUser' GO

SQL SERVER – Create Login with SID – Way to Synchronize Logins on Secondary Server LoginWithSID-02

As we can see that SID is matching that’s why user is mapped to same login.

Solarwinds

Now, if we create AlwaysOn Availability Group or configure database mirroring or log shipping – we would not be able to map the user using sp_change_user_login because secondary database is not writeable, its only read-only mode.

Here is what we would see on secondary server if login is created using UI.

SQL SERVER – Create Login with SID – Way to Synchronize Logins on Secondary Server LoginWithSID-03

The solution of this would be to drop and create login with known SID which is stored in sys.sysusers in the database.

CREATE Login AppUser WITH password = 'password@123', SID = 0x59B662112A43D24585BFE2BF80D9BE19

Once this is done, application can connect to secondary database. Hopefully this is something which would help you in fixing issue when there are orphan users in database.

Have you ever used this syntax? Leave a comment and let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Presenting 3 Technology Session at GIDS 2015
Next Post
Interview Question of the Week #016 – How to Take Database Offline

Related Posts

22 Comments. Leave new

  • Hello,

    Thanx 4 providing that simple solution, but if u have a lot of sql logins needs 2 b transferred; then I prefer to use the sp_help_revlogin described here https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server in which it will generate a login script that has the original SID & original password, then connect to the secondary replica instance and exec that script.

    I tried that script many times, it`s working perfectly fine & flawless.

    Thanx
    Hany Helmy

    Reply
  • Whenever I have needed to copy users from one server to another I have used this script. It uses the same syntax as what you suggest here, but wrapped up nicely into a stored procedure.

    Reply
  • You have SID how do you find user name belongs to this SID

    Reply
  • Hi Pinal,
    Instead of using commands is there any to do this graphically in sql server?

    Reply
  • Jonathan Bourgeois
    March 9, 2016 2:27 am

    Is it possible to do something like :

    alter login with sid = @Sid

    Thanks!

    Reply
  • Very nice content. Thank you.

    Reply
  • I am trying your solution for creating a user for a standby/readonly database that is on the primary. I got the original SID from the primary, created the userid on the secondary (in master) but am still not seeing any privileges to the readonly database. Will that show up when the logs are shipped overnight? What am I missing?

    Reply
  • Kopeka Simposya
    February 15, 2018 4:30 pm

    Exactly the solution I was looking for. Experienced the same problem trying to synchronize logins on my secondary server.

    Reply
  • Okay, I don’t want to bore anyone because I’ve said this about another Pinal Dave post… but when I googled for the issue I needed to solve and saw Pinal’s solution come up first, I knew it was going to be a very simple solution, that it would be complete and would not have anything you don’t need. And all served up with a great demeanor and attitude. I used the query to retrieve the SID from the Primary, pasted it in the Create on the Secondary, and the new Linked Server using that account was working immediately… Thanks, again.

    Reply
  • Hi My DBA Master Dave sir,

    I am little bit confusing about users in Always on please help me to clarify..

    1. If I create an user/login in one AOG database on Primary replica , is the user/login will transfer to secondary read only replica?

    2. Will the user get same roles like primary db side (created user and grnted R, W permissions).

    3. In case the user will be sync to secondary replica ,is the user is orphan ?

    Thanks,
    Mahendra

    Reply
    • 1. No. Login is part of master database so it won’t be created on secondary. User is part of database so it would reach on secondary.
      2. User would have same permission on database as primary
      3. create login with SID for SQL Login. Don’t worry about windows accounts.

      Reply
  • David Linares
    May 15, 2019 12:35 am

    You know that case when the User has the same SID but in the secondary I cannot see the correct mapping to the database? It happened to me. I checked and both users in both node have the same SID but the mapping is not syncronized.
    Thanks!!

    Reply
  • I have tried this solution .. I have created a new login on server 1 which is part of sql always on high availability group. when I am trying to create the same login in server 2 specifying the SID from server 1, it says SID already exists?

    Reply
  • The ‘Copy-DbaLogin’ powershell commandlet from the fine folks at dbatools.io will work for this task as well.

    Reply
  • Thanks for the help

    Reply

Leave a Reply

Menu