patternsqlModerate
Logins aren't syncing across Availability Groups
Viewed 0 times
groupsloginsarenavailabilityacrosssyncing
Problem
We have 2 servers in an AlwaysOn group.
While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.
Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).
How do I overcome this issue? Was I supposed to set up the user account in a special way?
While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.
Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).
How do I overcome this issue? Was I supposed to set up the user account in a special way?
Solution
My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.
Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.
Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.
Context
StackExchange Database Administrators Q#29903, answer score: 15
Revisions (0)
No revisions yet.