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.
- Log Shipping (reading from stand-by secondary database)
- AlwaysOn Availability Group. (reading from read-only secondary replica)
- 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
As we can see that SID is matching that’s why user is mapped to same login.
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.
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)
26 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
Thanks Hany.
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.
@Davin – Thanks for Sharing
You have SID how do you find user name belongs to this SID
Select * from sys.server_principals where SID = 0x…………………. is would guess
Correct.
Hi Pinal,
Instead of using commands is there any to do this graphically in sql server?
No.
Is it possible to do something like :
alter login with sid = @Sid
Thanks!
Very nice content. Thank you.
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?
Exactly the solution I was looking for. Experienced the same problem trying to synchronize logins on my secondary server.
Great. Nice to hear that.
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.
… and I thank you sir for kind note.
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
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.
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!!
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?
The ‘Copy-DbaLogin’ powershell commandlet from the fine folks at dbatools.io will work for this task as well.
Thanks for the help
I got below error.
Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is too short.
Which means you are password strength is less than 8 characters.
use CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF in the create login after SID; you will be able to create the login.
Thank you.
Dave, you saved me again. Thanks Pandit of SQL