Azure SQL Database- Create Login with Primary Database Login SID

Sometimes, the Azure SQL Database to be added is a Secondary Database, read-only replica of a Primary Database.

In these cases, to create the AimBetter login to connect to this Secondary Database, you’ll need to add the Primary Database Login SID.

First, you can check if the Database is a Primary or Secondary Database, running the following script:

SELECT TOP 1 CASE replica_role WHEN 1 THEN 'SECONDARY' ELSE 'PRIMARY' END FROM sys.dm_db_resource_stats
ORDER BY 1 DESC

The output of this script will indicate if the Database is a Primary or Secondary one.

Then, run the following script in the Primary Database:

SELECT SID FROM SYS.DATABASE_PRINCIPALS WHERE NAME = 'AimBetter'

If there is no output, it is because there is no AimBetter login in the Primary Database. In this case, create an AimBetter login. Then run again the above script.

You’ll receive as an output the Login SID of the Primary Database.

On the Secondary Database, run the following script to create the AimBetter user, changing the Password and SID number with the right values.

IF NOT EXISTS (SELECT * FROM SYS.SQL_LOGINS  WHERE NAME ='AimBetter')
BEGIN
CREATE LOGIN [AimBetter] WITH PASSWORD= N'Password',SID=Primary_Database_SID
ALTER LOGIN [AimBetter] ENABLE
CREATE USER [AimBetter] FOR LOGIN [AimBetter]
END