NOTE: This does not apply to Windows logins, only SQL logins.
- Connect to the AlwaysOn listener. This ensures you are working with the primary node.
- Create the SQL Login and database mappings, security settings, etc.
- Next, run this query:
SELECT SUSER_SID ('USERNAME')
This will return the user's SID which is the unique identifier for the login. Even if the names match, unless the SID is the same the SQL login will not work properly on the secondary node after a failover.
- Connect to the secondary node. Be sure to check/refresh the Availability Group to ensure you are working with the correct node.
- Copy the SID into the following query, also update the username and default database as needed. Be sure to use the same password as you did when first creating the user.
CREATE LOGIN [USERNAME] WITH PASSWORD=N'PASSWORD', SID=[SID from prior query], DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
This should create the SQL login on the secondary node with the same username, password, SID and default database.
- If you wish to test, open the Availability Group dashboard, ensure it is "all green" and then initiate a manual failover. This will make the secondary the primary and vice versa.
- Test your new login.