Contained users in SQL Azure DB V12
In Microsoft Azure SQL Database, when you sign up for the service, the provisioning process creates an Azure SQL Database server, a database named master, and a login that is the server-level principal of your Azure SQL Database server. That login is similar to the server-level principal, sa, for an instance of SQL Server on your premises.
Every login to the database, hits the master database and performs authentication / authorization checks and firewall rule checks. This is not very efficient as in SQL DB master and user can sit on two different SQL servers potentially in two different machines. Also when a server has multiple user databases then master will be the bottleneck in the login process, and under load this may result in high response time for logins. If Microsoft is updating the software on the machine / server then master will be unavailable for a few seconds and all the logins to the user database can fail too at this time. Please note that this can take upto a few seconds.
SQL DB V12 now supports contained user and one can minimize dependency on master for their applications by using the contained users and database firewall rules instead of server firewall rules. So that the login requests won’t reach the master database and the database will be contained.
Syntax:
Create user ContainedUser with password = ‘Password’
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [ContainedUser]
ALTER ROLE [db_owner] ADD MEMBER [ContainedUser]