Syncing Logins Between AG Replicas

Daniel Hutmacher has a new series on Availability Group synchronization, starting with logins:

You’ll need a linked server from your secondary replica to the primary replica. This linked server should feature the absolute bare-minimum of permissions, preferably with only the “be made using the login’s current security context” selected.

The account running the procedure (or the mapped login in the linked server) will need SELECT access to the following DMVs on the remote (primary) server:

  • master.sys.server_principals

  • master.sys.sql_logins

  • master.sys.server_role_members

  • master.sys.server_permissions

The code itself is a download from Daniel’s website; go check it out.

Related Posts

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING Wait Type

Chirag Shah explains what the HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING wait type really means: Recently a customer reported an interesting issue, while querying against recently added readable replica, SELECT statement is shown as suspended and session is shown as waiting on HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING […] Upon more investigation, it appeared to be waiting on with a wait type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING The behavior is by […]

Read More

Managing Central Management Server

Chrissy LeMaire shows how you can use dbatools to manage Central Management Server and registered servers: It’s a super useful feature that not all DBAs know about. Since CMS data is stored in msdb and accessible via SMO, you can access it from SQL Server Management Studio or PowerShell modules like dbatools. Central Management Server’s essential […]

Read More

Categories

October 2016
MTWTFSS
« Sep Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31