How to create SQL logins in AlwaysOn Environment

       How to create Login’s in case of AlwaysOn Database Server.

-------------------------------------------------------------------------------------------------------------------------------------------------

The Process is:-

Availability group (SQL0040AAG01) has 4 replica names

1.            SQLNode1 -- Primary

2.            SQLNode2  -- Secondary

3.            SQLNode3  -- -- Secondary

4.            SQlNode4 -- -- Secondary

Whereas SQLNode1 is Primary replica and other 3 are secondary replica.

Then, the logic is for other 3 replica login  SID should match...

/* On the PRIMARY replica */ [SQLNODE1] USE [master] GO CREATE LOGIN [TestDBA] WITH PASSWORD=N'Test@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [Adven_pub] GO CREATE USER [TestDBA] FOR LOGIN [TestDBA] GO USE [Adven_pub] GO ALTER ROLE [db_datareader] ADD MEMBER [TestDBA] GO USE [Reservation] GO CREATE USER [TestDBA] FOR LOGIN [TestDBA] GO

In primary replica  using this site – > https://support.microsoft.com/en-us/kb/918992

get an script for sp_help_revlogin 

Execute this complete stored procedure in master database, from Primary replica

then In primary replica  use below command :-

sp_help_revlogin 'TestDBA'

you will get an output like this :-

/* sp_help_revlogin script 

** Generated Sep 30 2015 12:47PM on SQLNode1 */


-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Connect SQLNode2 

copy create login script  to secondary replica:-

and execute this in 

use master 

go

-- Login: TestDBA
CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Permissions will get replicated automatically to secondary replica using AlwaysOn methodology.

Replicate the same process in another secondary replica for that database... .

If user exists in secondary replica and no one is using this login or password mismatch error comes or orphan login issue comes  then

Connect  secondary replica SQLNode2 

Copy this to secondary replica:-

Use master 

go

DROP LOGIN [TestDBA]

GO

-- Login: TestDBA

CREATE LOGIN [TestDBA] WITH PASSWORD = 0x0200670719A986569F7AC81D280F060E1DF59E55DE89DD83905F9443DD752329EC77AA7038C6D816D46A07C952F9D7F2BF7DEFDFB5744659F566543A790624712C82A457BBA3 HASHED, SID = 0xC6E83AFC0F3A674BB70D0279A60291DB, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Thanks for reading and Happy Learning :)