This article describes
Creating a SQL Login with SQL Server Availability Groups or How do I create a login in SQL Server Availability Group?
Logins in SQL Server:
There are 2 types of logins can be created in SQL Server.
Login with windows authentication: These users first need to be created in Active Directory and then we have to create same user in SQL Server and grant required permission. In this login user authenticate with SQL Server using active directory authentication method mostly Kerberos authentication.
Login With SQL Server Authentication: This is SQL user created in SQL server with password. We can set password policy and expiration in this SQL user. We have to use SQL authentication while connecting to SQL Server. Granting permission is same as assigning permission to windows login.
Creating a SQL Login with SQL Server Availability Groups
Always On in SQL Server: SQL Server has many high availability techniques. Always introduced from SQL Server 2012 with 2 nodes AG group and now it allows to create 8 replicas in SQL Server enterprise edition. Always On is extended feature of SQL Server Database Mirroring along with failover cluster feature.
You can user always on as both High Availability and Disaster recovery techniques. You can user a sync i.e., secondary replica for read queries to reduced load on primary i.e., Production SQL Server.
We can create user on a sync i.e., secondary replica using same SID and grant read only permission on primary which automatically get mapped to secondary replica as user has same SID on secondary replica.
How do I create a login in SQL Server Availability Group?
Step 1: Connect SQL Server instance of primary replica in Always ON and open SSMS.
Step 2: Connect to new query window and go to query tab in SSMS and enable SQLCMD mode.
Step 3: Copy and paste below query in query window and make sure that all replica of AG group should connecting from that server to create SQL login with sid.
--Enable SQLCMD in query option before execution
--Change Username
USE [master]
GO
CREATE LOGIN[ABC] WITH PASSWORD=N'ya7Dpmz^ze#S', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER LOGIN[ABC] DISABLE
GO
SELECT * FROM sys.syslogins WHERE name = 'ABC'
--Copy SID from above query and paste it in SID of below Query
--Change server name here and make sure all replica in AG should connect from from SSMSv
: CONNECT ServerName
USE [master]
GO
CREATE LOGIN [ABC] WITH PASSWORD=N'ya7Dpmz^ze#S', DEFAULT_DATABASE=[master], SID=0xF3EBE18C1FA1BE4FA7360BF7965FAA32, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER LOGIN[ABC] DISABLE
GO
: CONNECT ServerName
USE [master]
GO
CREATE LOGIN[ABC] WITH PASSWORD=N'ya7Dpmz^ze#S', DEFAULT_DATABASE=[master], SID=0xF3EBE18C1FA1BE4FA7360BF7965FAA32, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER LOGIN [ABC] ENABLE
GO
Step 4 : Verify if all users created on respective replica which part of SQL Server AG.
How to configure disk space alert on Azure SQL Managed Instance?