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)