In an Always On Availability Group (AG) environment, SQL logins must be configured with consistent Security Identifiers (SIDs) across all replicas to avoid issues like orphaned users and ensure seamless authentication during failovers.

In this blog post we’ll demo creating a SQL login, replicating it to secondary replica with the same SID, and assigning the necessary permissions.

Step 1: Create the SQL Login on the Primary Replica

1. Connect to the Primary Replica
Use SQL Server Management Studio (SSMS) to connect to the primary replica of your AG environment.

2. Create the Login
Run the following T-SQL to create the login:

CREATE LOGIN [YourLoginName] WITH PASSWORD = 'YourStrongPassword'; 

3. Retrieve the Login SID
Retrieve the SID of the newly created login using one of these methods:

-- Using SUSER_SID function
SELECT SUSER_SID('YourLoginName') AS LoginSID; 

-- Alternatively, retrieve it from sys.server_principals 
SELECT sid AS LoginSID 
FROM sys.server_principals 
WHERE name = 'YourLoginName'; 

Save this SID for creating the login on the secondary replicas.

Step 2: Create the SQL Login on the Secondary Replica

1. Connect to the Secondary Replica
Use SSMS to connect to the secondary replica.

2. Create the Login with the Same SID
Use the SID retrieved from the primary replica to create the login on the secondary:

CREATE LOGIN [YourLoginName] WITH PASSWORD = 'YourStrongPassword', SID = 0xYourSIDValue; 

Replace 0xYourSIDValue with the exact SID retrieved from the primary replica.

3. Verify the Login SID
Confirm the SID matches by running:

SELECT SUSER_SID('YourLoginName') AS LoginSID; 

Step 3: Assign Permissions to the Login

1. Grant Database Access
Map the login to a database user in the relevant databases:

USE [YourDatabaseName]; 
CREATE USER [YourLoginName] FOR LOGIN [YourLoginName]; 

2. Assign Roles
Grant the necessary database roles to the user. For example:

USE [YourDatabaseName]; 
ALTER ROLE db_datareader ADD MEMBER [YourLoginName]; 
ALTER ROLE db_datawriter ADD MEMBER [YourLoginName]; 

3. Grant Additional Permissions
For server-level permissions, such as VIEW SERVER STATE, explicitly grant them on both the primary and secondary replicas:

GRANT VIEW SERVER STATE TO [YourLoginName];

Testing and Best Practices

To test the login, simply connect to the AG listener in SSMS using the credentials for the login. This verifies that the login was created correctly and resolves any potential orphaned user issues.

Tip: When connecting to AG environments, include the MultiSubnetFailover=True parameter in your connection string. This ensures optimal failover performance in multi-subnet deployments.

While you wouldn’t typically perform a failover just to test logins, regular failover testing as part of a disaster recovery plan can help confirm that logins remain functional and avoid orphaned SQL login issues during actual failovers.