If you have an single Azure SQL server and you want to create read only account. This is very simple


Step 1: Connect to Primary master DB and click on new query then create login

create login <loginname> with password='Nam@012'

Step 2: Connect to user DB and click on new query then create user (also assign permision)

create user <username> from login <loginname>

EXEC sp_addrolemember 'db_datareader', '<username>';

But if you have Azure SQL server and it has already setup failover group. How to do?

Step 1: Connect to Primary master DB and click on new query then create login

create login <loginname> with password='Nam@012'

Step 2: Connect to user DB and click on new query then create user (also assign permision)

create user <username> from login <loginname>

EXEC sp_addrolemember 'db_datareader', '<username>';

Step 3: Execute below query on user DB to fetch the Sid

 SELECT [sid] FROM sysusers WHERE [name] = 'loginname';

Step 4: Connect to Secondary master DB and click on new query then create login with sid

CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx', SID = [SID of loginname login on source server];


Reference

https://learn.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-sql-db?view=azuresql-db&tabs=azure-powershell

https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql-mi