How to create users in databases that belong to a SQL Server Always On Availability Group

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
• How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
• How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025

When we create new users in databases that belong to Always On Availability Group in SQL Server we want them to work even in case of failover to Secondary node, but when we create a new login in a SQL Server instance it is not automatically created on Secondary nodes. So in this article we will see how we should create logins.

How do we transfer logins to Secondary Nodes?

After creating a SQL Login on the Primary node, we run the following query on the Primary node by putting where the name of the login we created so that the DDL script that creates the login can be created for us:

SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
    CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+
    N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';',
    N'ALTER LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED UNLOCK ,CHECK_POLICY=OFF;'
FROM master.sys.server_principals AS sp
INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.name = 'test';  -- Το αλλάζουμε ανάλογα με το όνομα του χρήστη που θέλουμε να μεταφέρουμε

After running it, we select the first column with the result and run the DDL on the Secondary nodes to create the login there as well:

How are rights transferred?

We do not forget that after we create the logins on all Nodes, any permissions we grant to the Primary node at the database level will automatically be transferred to the Secondary nodes, e.g. db_owner, db_datareader, db_datawriter, etc., however, permissions at the instance level, e.g. sysadmin, are not automatically transferred.

What do we do if we change the password in SQL Logins?

If we change the password of a SQL Login on the Primary node, then we run the results of the second column of the script that performs ALTER LOGIN with the new password on the Secondary Nodes.