Sync SQL Logins and Jobs

ryanjadams · ‎Mar 15 2019

First published on MSDN on Aug 21, 2017
In this post I'll point you to some options to sync SQL logins and then I'll demo my favorite option in a video. If you are using Availability Groups or Mirroring you know you need to sync SQL logins and jobs among replicas. What are your options to sync SQL Logins and maybe even jobs? Let's talk about some options so you know what your choices are and then I'll show you what I suggest.

Sync SQL Logins

Method 1

We provide a couple of stored procedures you can use. It's not automated, but you could automate it yourself. Once you create the SPs and run sp_help_revlogin it gives you the scripts to run on the partner to create the logins. It was designed for a 1 time transfer, but again, you can automate this.

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instan...

Method 2

You can use SSIS to sync SQL logins. This method has some gotchas, but I'll leave that to the reader. The good news is that it is automated.

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/transfer-logins-task

Method 3

Robert Davis wrote a great script back when he published his Mirroring book. I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins. His reply was something along the lines of, "What are you talking about? Of course it handles SQL logins". It turns out that the publisher didn't get the right script version published with the book. That's when this post from Robert with the full script was born...

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers. If you can't have linked servers in your environment this is not a good choice for you. However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer. It breaks the rule but it does it fast enough maybe no one will notice.

Method 4

Contained databases. A contained database keeps the entire user authentication mechanism within the database without relying on logins that sit outside the database in Master. You can read more about contained databases here.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

Method 5

There is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire . This method is my personal choice. It works great and is easy to automate. You can run it with SQLAgent or you can just use Scheduled Tasks in the OS. The scheduled tasks method is a little cleaner, but you don't get to see it in SQL Server. Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.

The advantage here is that you can automate a lot more than than just logins. In fact you can migrate and automate pretty much anything at the server level. Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.

https://dbatools.io/

SQL Agent Job Code

/*
This script creates a job to copy and sync all logins in a 3 node Availability Group test environment.
User accepts all risks.  Always test in a test environment first.
Ryan J. Adams
http://www.ryanjadams.com/go/AGSync

In order for this to work you HAVE to install dbatools from http://dbatools.io
This MUST be done on all replicas.
Run this from an elevated PowerShell to install it.
Try this first
	Install-Module dbatools
If that doesn't work try this
	Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://git.io/vn1hQ)

The last option will not make it available to the SQLAgent service account so copy it from your profile to its profile.
Copy the dbatools folder 
FROM   C:\Users\MyProfile\Documents\WindowsPowerShell\Modules
TO   C:\Users\SQLAgentProfile\Documents\WindowsPowerShell\Modules
	
  ***** UPDATE *****
	 Copy it here instead so that all users can access it.
	 C:\Windows\System32\WindowsPowerShell\v1.0\Modules
  ******************

--Here is what the code does after you install DBATOOLS
This will create a function to determine if the node it is running on is currently the primary.  It only creates this function on SQL2012.
It then creates a job with 3 steps.  The first uses the function to determine if it is primary. If it is we continue and if not we raise an error to exit the job.
That job step is set to quit reporting success even if it fails.
Step 2 copies any logins from the partner that do not already exist including all permissions.
Step 3 sync permissions for all existing logins.

YOU NEED TO CHANGE THE TARGET DB, SOURCE INSTANCE, AND DESTINATION INSTANCE IN THE JOB STEPS
THE JOB MUST RUN UNDER AN ACCOUNT THAT IS SYSADMIN ON ALL REPLICAS.  IT CAN BE THE SQL AGENT SERVICE ACCOUNT OR A PROXY ACCOUNT.

*/

:CONNECT NODE1
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
--This proc alone was written by Patrick Keisler
BEGIN
	IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
		DROP FUNCTION dbo.fn_hadr_is_primary_replica;
	
	DECLARE @SQL nvarchar(MAX) 
	SET @SQL = '
	CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
	RETURNS TINYINT
	WITH EXECUTE AS CALLER
	AS
	/********************************************************************
		File Name:    fn_hadr_is_primary_replica.sql
		Applies to:   SQL Server 2012
		Purpose:      To return either 0, 1, 2, or -1 based on whether this
					@DatabaseName is a primary or secondary replica.
		Parameters:   @DatabaseName - The name of the database to check.
		Returns:      0 = Resolving
					1 = Primary
					2 = Secondary
					-1 = Database does not exist
		Author:       Patrick Keisler
		Version:      1.0.1 - 07/03/2015
		Help:         http://www.patrickkeisler.com/
		License:      Freeware
	********************************************************************/
	BEGIN
		DECLARE @HadrRole TINYINT;
		IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
		BEGIN
			-- Return role status from sys.dm_hadr_availability_replica_states
			SELECT @HadrRole = ars.role
			FROM sys.dm_hadr_availability_replica_states ars
			INNER JOIN sys.databases dbs
				ON ars.replica_id = dbs.replica_id
			WHERE dbs.name = @DatabaseName
			-- @DatabaseName exists but does not belong to an AG so return 1
			IF @HadrRole IS NULL SET @HadrRole = 1
			RETURN @HadrRole
		END
		ELSE
		BEGIN
			-- @DatabaseName does not exist so return -1
			SET @HadrRole = -1
		END
	RETURN @HadrRole
	END'
	EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA') 
EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description='If this replica is currently the primary it will copy and sync any new accounts.  It will also sync any permissions changes.  If not the primary it will raise an error.', 
		@category_name='Availability Group Sync', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=1, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
	RAISERROR (N''Node is not primary.  Error raised to exit job gracefully. This error can be safely ignored.'',
	16, -- Severity,  
	1) -- State
END
', 
		@database_name=N'master', 
		@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE1 to NODE2', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node1 -Destination Node2"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE1 to NODE2', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node1 -destination Node2"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE1 to NODE3', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node1 -Destination Node3"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE1 to NODE3', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node1 -destination Node3"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20160805, 
		@active_end_date=99991231, 
		@active_start_time=190000, 
		@active_end_time=235959, 
		@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/************************************
Now we go create the job on NODE2
*************************************/
:CONNECT NODE2
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
--This proc alone was written by Patrick Keisler
BEGIN
	IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
		DROP FUNCTION dbo.fn_hadr_is_primary_replica;
	
	DECLARE @SQL nvarchar(MAX) 
	SET @SQL = '
	CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
	RETURNS TINYINT
	WITH EXECUTE AS CALLER
	AS
	/********************************************************************
		File Name:    fn_hadr_is_primary_replica.sql
		Applies to:   SQL Server 2012
		Purpose:      To return either 0, 1, 2, or -1 based on whether this
					@DatabaseName is a primary or secondary replica.
		Parameters:   @DatabaseName - The name of the database to check.
		Returns:      0 = Resolving
					1 = Primary
					2 = Secondary
					-1 = Database does not exist
		Author:       Patrick Keisler
		Version:      1.0.1 - 07/03/2015
		Help:         http://www.patrickkeisler.com/
		License:      Freeware
	********************************************************************/
	BEGIN
		DECLARE @HadrRole TINYINT;
		IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
		BEGIN
			-- Return role status from sys.dm_hadr_availability_replica_states
			SELECT @HadrRole = ars.role
			FROM sys.dm_hadr_availability_replica_states ars
			INNER JOIN sys.databases dbs
				ON ars.replica_id = dbs.replica_id
			WHERE dbs.name = @DatabaseName
			-- @DatabaseName exists but does not belong to an AG so return 1
			IF @HadrRole IS NULL SET @HadrRole = 1
			RETURN @HadrRole
		END
		ELSE
		BEGIN
			-- @DatabaseName does not exist so return -1
			SET @HadrRole = -1
		END
	RETURN @HadrRole
	END'
	EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA') 
EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description='If this replica is currently the primary it will copy and sync any new accounts.  It will also sync any permissions changes.  If not the primary it will raise an error.', 
		@category_name='Availability Group Sync', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=1, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
	RAISERROR (N''Node is not primary.  Error raised to exit job gracefully. This error can be safely ignored.'',
	16, -- Severity,  
	1) -- State
END
', 
		@database_name=N'master', 
		@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE2 to NODE1', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node2 -Destination Node1"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE2 to NODE1', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node2 -destination Node1"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE2 to NODE3', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node2 -Destination Node3"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE2 to NODE3', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node2 -destination Node3"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20160805, 
		@active_end_date=99991231, 
		@active_start_time=190000, 
		@active_end_time=235959, 
		@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/************************************
Now we go create the job on NODE3
*************************************/
:CONNECT NODE3
USE [master];
GO

IF (SELECT LEFT(CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion')),2)) = 11 --This function exists on versions above 2012 and below 2012 AGs did not exist.
BEGIN
--This proc alone was written by Patrick Keisler
	IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
		DROP FUNCTION dbo.fn_hadr_is_primary_replica;
	
	DECLARE @SQL nvarchar(MAX) 
	SET @SQL = '
	CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
	RETURNS TINYINT
	WITH EXECUTE AS CALLER
	AS
	/********************************************************************
		File Name:    fn_hadr_is_primary_replica.sql
		Applies to:   SQL Server 2012
		Purpose:      To return either 0, 1, 2, or -1 based on whether this
					@DatabaseName is a primary or secondary replica.
		Parameters:   @DatabaseName - The name of the database to check.
		Returns:      0 = Resolving
					1 = Primary
					2 = Secondary
					-1 = Database does not exist
		Author:       Patrick Keisler
		Version:      1.0.1 - 07/03/2015
		Help:         http://www.patrickkeisler.com/
		License:      Freeware
	********************************************************************/
	BEGIN
		DECLARE @HadrRole TINYINT;
		IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
		BEGIN
			-- Return role status from sys.dm_hadr_availability_replica_states
			SELECT @HadrRole = ars.role
			FROM sys.dm_hadr_availability_replica_states ars
			INNER JOIN sys.databases dbs
				ON ars.replica_id = dbs.replica_id
			WHERE dbs.name = @DatabaseName
			-- @DatabaseName exists but does not belong to an AG so return 1
			IF @HadrRole IS NULL SET @HadrRole = 1
			RETURN @HadrRole
		END
		ELSE
		BEGIN
			-- @DatabaseName does not exist so return -1
			SET @HadrRole = -1
		END
	RETURN @HadrRole
	END'
	EXECUTE sp_executesql @SQL;
END
GO

USE [msdb];
GO

--Before we create the job we need an operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBA') 
EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'DBA@mycompany.com';
GO

--Create the category
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name='Availability Group Sync' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name='Availability Group Sync'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

--Create the job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Copy and Sync Logins', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description='If this replica is currently the primary it will copy and sync any new accounts.  It will also sync any permissions changes.  If not the primary it will raise an error.', 
		@category_name='Availability Group Sync', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Verify Primary Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Verify Primary', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=1, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'IF sys.fn_hadr_is_primary_replica(''App1AG_DB1'') <> 1 --If it is NOT the primary
BEGIN
	RAISERROR (N''Node is not primary.  Error raised to exit job gracefully. This error can be safely ignored.'',
	16, -- Severity,  
	1) -- State
END
', 
		@database_name=N'master', 
		@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE3 to NODE1', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node3 -Destination Node1"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE3 to NODE1', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node3 -destination Node1"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Copy Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Logins NODE3 to NODE2', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Copy-DbaLogin -Source Node3 -Destination Node2"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create the Sync Logins Job Step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Logins NODE3 to NODE2', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -Command "Sync-DbaLoginPermission -source Node2 -destination Node3"', 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:00PM', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20160805, 
		@active_end_date=99991231, 
		@active_start_time=190000, 
		@active_end_time=235959, 
		@schedule_uid=N'63580255-7f51-4cd6-ad43-eb8ebc646350'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Powershell Job Code

# This script creates a job to copy and sync all logins in a 3 node Availability Group test environment.
# User accepts all risks.  Always test in a test environment first.
# Ryan J. Adams
# http://www.ryanjadams.com/go/AGSync

Import-Module SQLPS -DisableNameChecking
$AGName = "App1AG"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server
$AGPrimary = $Server.AvailabilityGroups[$AGName].DatabaseReplicaStates | Where-Object {$_.ReplicaRole -eq "Primary"} | Select-object -property AvailabilityReplicaServerName -First 1

#Write-Host $AGPrimary.AvailabilityReplicaServerName
#write-host $env:COMPUTERNAME

If ($AGPrimary.AvailabilityReplicaServerName -eq $env:COMPUTERNAME) {

    Copy-SqlLogin -Source Node1 -Destination Node2
    Sync-SqlLoginPermissions -source Node1 -destination Node2
    
    Copy-SqlLogin -Source Node1 -Destination Node3
    Sync-SqlLoginPermissions -source Node1 -destination Node3}

Else {Exit}

altsysrq · ‎Jun 18 2019

Are there more methods for addressing password synchronization? Method 5 is actually amazing but none of those tools will actually synchronize passwords after a user has already been created.

jdbrown239 · ‎May 20 2020

Great post Ryan

Will this also work on Basic Availibility Groups since replicas on the secondary are unavailible?

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs