I always follow a contained user model when setting up users within my Azure SQL Database. I do this so the user in question has access to only specific database(s) and does not have a login to the server. It becomes even more apparent the importance of this when you design a solution based on failover groups.

When a failover occurs to the secondary, I want a pleasant experience for the user. With the contained user model, the user goes with the database. I don’t want to do admin work on the new primary (post failover). Let’s see.

Here I have setup a failover group.

Within my primary server (spacesql) I have 2 different logins / users. A traditional one (called NormalUser) and a contained user (ArunContained).

-- Create a new classic login and give them a password
CREATE LOGIN NormalUser WITH PASSWORD = ‘’

-- Create a new SQL user from that login
CREATE USER NormalUser FOR LOGIN NormalUser;

ALTER ROLE db_datareader ADD MEMBER NormalUser;

--Contained User
CREATE User ArunContained WITH PASSWORD = ‘’

ALTER ROLE db_datareader ADD MEMBER ArunContained;

I initiate a failover. What happens to my user experience?

I connect to the main read/write endpoint, don’t forget underneath the covers I am actually pointing to the secondary (new primary).

Let’s login. (routing to testdb).

No issues – nice and smooth.

The traditional login?

Not a nice experience, right?  Some manual intervention is needed here to sync the user / login across.