Add user to a secondary replica in SQL 2012 AlwaysOn

Question

_{Thursday, August 13, 2015 4:24 PM}

I want to add a new AD user to give access to the database that is in the Always on availability group. So here is what I did:

1. Added the AD user in the primary replica  & gave access to the db

2. Then added that user on the secondary replica. 

But that user is not getting synchronized on the db that's in the secondary replica. 

Also the master db is not set to be synchronized. Please let me know what I am missing, so that the user can be added to the db on the secondary replica.

Thanks.

sqldev

All replies (2)

_{Thursday, August 13, 2015 4:36 PM ✅Answered}

https://msdn.microsoft.com/en-us/library/hh413247.aspx?f=255&MSPPError=-2147217396

Did u failed over and check if the user is able to connect to secondary after failover ?

The above article will walk you thru to check if you are missing anything.

Hope this helps ! Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

---

_{Thursday, August 13, 2015 4:56 PM ✅Answered}

1. Create a new login on the PRIMARY replica of an existing Availability Group

2. Grant privileges to this login if necessary. They will NOT automatically replicate to the SECONDARY replica. Now find the SID of this new login.

3. On the SECONDARY replica, create the login with the same SID. The GUI wizard to create logins does not have this feature to specify the SID, so the login has to be created using TSQL with an additional parameter.

4. Grant the same privileges to this login as done on the PRIMARY replica.

5. Now go back to the PRIMARY replica and create database user mapped to the login and grant required permissions at the database level. This new database user will be automatically replicated on the SECONDARY replica with its permissions and correctly map to the login. No action on SECONDARY required because the user database is in an Availability Group that is synced across replicas.