In this article, we will discuss how to transfer logins to the secondary replica of an Always On availability group in SQL Server. When configuring a new secondary replica, it is important to move the logins that are created on the primary replica. Failure to do so can result in login errors for users after failover to the secondary replicas.

To demonstrate the scenario, let’s consider an availability group with the following details:

• Primary Replica: SQL01
• Secondary Replica: SQL02
• Secondary Replica: SQL03

On the primary replica, we have created a SQL Login named NUpadhyay with the bulkadmin fixed server role. To obtain the information about the login, we can execute the following query:

SELECT sp.name AS [Name of Role], member.name AS [Name of Member]
FROM sys.server_role_members srm
JOIN sys.server_principals AS sp ON srm.role_principal_id = sp.principal_id
JOIN sys.server_principals AS member ON srm.member_principal_id = member.principal_id;

After performing a planned manual failover, where SQL02 becomes the primary replica and SQL01 becomes a secondary replica, we encounter an error when trying to login using the NUpadhyay login. This issue can be resolved by recreating the same login on the secondary replicas.

There are several approaches to transfer logins to the secondary replica:

1. SSIS Transfer Login Task

We can use the Transfer Login task in the SQL Server Integration Services package to transfer logins. To do this, we need to create a new integration services project in SQL Server Data Tools, and then drag and drop the Transfer Login Task onto the task flow window. We can configure the task by specifying the source (SQL01) and destination (SQL02) servers, and selecting the logins to transfer. Once the package executes successfully, the logins will be copied from the source server to the destination server.

2. Create sp_help_revlogin and sp_hexadecimal SP

We can use the stored procedures sp_help_revlogin and sp_hexadecimal to generate a CREATE LOGIN script for all the SQL Logins on the primary replica. The sp_help_revlogin procedure generates the CREATE LOGIN statement, while the sp_hexadecimal procedure converts the password hash into a text file. By using these procedures, we can generate the T-SQL code to recreate the SQL Logins on the secondary replicas.

To use these procedures, we need to create them in the DBA utility database. Once created, we can execute them using the exec <storedprocedure_name> command. The output of the sp_help_revlogin procedure will provide us with the CREATE LOGIN command for each SQL Login. We can filter out logins that are not required to be recreated on the secondary replica, such as system logins, using a temporary table and a while loop.

Once the CREATE LOGIN commands are generated, we can connect to the secondary replica (SQL02) and execute the script to create the logins. After this step, the logins should be able to connect to the availability group.

By following these approaches, we can efficiently transfer logins to the secondary replica of an Always On availability group in SQL Server.

Thank you for reading this article. We hope you found it helpful in understanding the process of transferring logins in SQL Server.