SQL in Cloud

Recent Posts

  • Inconsistent database ids in SQL Azure V12
  • Taking database offline SQL DB
  • Minimize login time in SQL DB V12
  • Full-text search in SQL Azure
  • Alter database – SQL Azure V12

Recent Comments

    Archives

    • May 2016
    • December 2015
    • July 2015
    • May 2015
    • April 2015

    Categories

    • SQL Azure
    • Uncategorized

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org

    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]

    Leave a comment Cancel reply

    Your email address will not be published. Required fields are marked *

    Pages

    • Alter database in SQL V12
    • Changing MAX_DOP setting in SQL Azure DB V12
    • Connection Pooling and Isolation level reset
    • Contained users in SQL Azure DB V12
    • Full-text search in SQL Azure
    • Large Index Rebuild in SQL Azure V12
    • Row versioning in SQL Database version (V12)
    • Sql Azure db – database offline

    Archives

    • May 2016
    • December 2015
    • July 2015
    • May 2015
    • April 2015

    Categories

    • SQL Azure (8)
    • Uncategorized (3)

    WordPress

    • Log in
    • WordPress

    Subscribe

    • Entries (RSS)
    • Comments (RSS)
    • Alter database in SQL V12
    • Changing MAX_DOP setting in SQL Azure DB V12
    • Connection Pooling and Isolation level reset
    • Contained users in SQL Azure DB V12
    • Full-text search in SQL Azure
    • Large Index Rebuild in SQL Azure V12
    • Row versioning in SQL Database version (V12)
    • Sql Azure db – database offline