Contained Database in SQL Server

This post is moreover interview question requirement then work related. I didn't see people actually using contained databases. 

Why we need Contained databases?

Normally logins are managed at the server level (as logins) and at the database level (as users), so they are defined in two locations.  The mapping that connects logins to users gets lost when a database is moved to a different server, which results in orphaned users.  When moving a database, the logins must be created on the new server, and must match exactly…otherwise this mapping will be broken.Orphaned users can also cause HA/DR to be compromised in this situation, because of trying to manage logins and users separately. Logins have to be synchronized against secondary or fail-over servers to avoid orphaned users.

                         With contained databases, you won’t run into the issue of orphaned users. Contained database users do not have to authenticate with the instance. They don’t have to have a corresponding server login. The contained database user has no dependency on the server. They will connect directly to the database.

For applications to connect, they have to specify the database in the application connection string, and include the parameter called ‘initial catalog’ in order to connect directly to the database.

Benefits with AlwaysOn – in case of failover, contained database users will be able to connect to the secondary without creating logins on the instance hosting the secondary.

Development benefits – because developers don’t always know on which instance their database will be deployed, creating a contained database can limit the environmental effects on the database, and can lessen the work and difficulties for the developer.

Administration – Database owners maintain the database settings in the contained database, instead of master database, so it avoids users having to be given sysadmin permissions.

Collation – temp objects are created with the collation of contained database in the case where the server collation is different from database.  There is no need to COLLATE anymore. There may be some problems if a session accesses both contained and non-contained databases

/**Enabled Advanced options**/

sp_configure ‘show advanced’, 1;

RECONFIGURE WITH OVERRIDE;

go

/**Enabled Database Containment on Server**/

sp_configure ‘contained database authentication’, 1;

RECONFIGURE WITH OVERRIDE;

go

/**Enabled Database Containment**//

USE MASTER

GO

ALTER DATABASE PrimaryDB SET CONTAINMENT=PARTIAL;

GO

/****For cross db connectivity****/

/****Finding SID of CommonUser SID in PrimaryDB*****/

USE PrimaryDB

GO

SELECT name,sid from sys.database_principals where name ='CommonUser'

GO

/*****DROP and RECREATE CommonUser user in SecondaryDB Database*****/

USE [SecondaryDB]

GO

DROP USER CommonUser

GO

CREATE USER [CommonUser]WITH PASSWORD=N'My$Test123', DEFAULT_SCHEMA=[dbo],

sid =0x01050000000000090300000022662BC1922D5C47895DB97345B7215A

GO

ALTER ROLE [db_datareader] ADD MEMBER [CommonUser]

GO

ALTER ROLE [db_datawriter] ADD MEMBER [CommonUser]

GO

Now we need to run the below trust command on both the databases

Alter Database xxxDB Set TRUSTWORTHY ON

GO

Post trust enabled between the databases, we will be able to query both the databases.

By Mohammed Adil