Azure SQL Database – Contained user Model

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 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.


1 thought on “Azure SQL Database – Contained user Model

  1. Pingback: Dew Drop – March 9, 2021 (#3397) – Morning Dew by Alvin Ashcraft

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s