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.