Hi @Sai ,
The blogs that pituach offered are good start for this question.
SQL server AG is a database level HA. A user created in a database on primary database will be carried over to the secondary database as it resides within the database. But SQL logins are Server-level principal, they will not automatically sync.
Windows/AD logins and groups already come with a SID . This means that you can create a login for a given Windows account on any number of servers, and all those logins will get the same SID. Windows logins on primary and secondary replicas has same SID.
But SQL Server logins generate a new SID when they are created, so if you just create a login on two servers, they’ll end up with different SIDs.
Follow below steps to sync logins in AG.
- Use below T-SQL generates a CREATE LOGIN script with password hash, SID for a login Cathy2 in primary replica.
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';'
FROM master.sys.server_principals AS sp
INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.name='Cathy2'

- Then copy this result script and execute it on the secondary . replicas to create the login with SID similar to the primary replica. 3... Use below T-SQL to check the logins and SID.
select name, sid, type_desc from sys.server_principals
If you want to get more detail information, please reading the blog Synchronize logins between Availability replicas in SQL Server Always On Availability Groups that pituach mentioned.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".