What is Contained Database in SQL Server

What is Contained Database ?
1.Contained database is a new feature launched in SQL Server 2011.
2.This model clearly separates the database application from the management of SQL Server. Users can connect to the database without authenticating a login at the Database Engine level.
3.Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management.

A Video Tutorial and a Presentation on Contained Databases can be downloaded from here http://sqlserver-training.com/video-sql-server-contained-database/-

Why Contained Databases feature was Required ?

One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of inside the database and When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind.

Later On, DBA’s nee to identify the data which was left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.

What all is being saved inside a Contained Database?

The contained database, keeps all necessary information and objects in the database, for example
•Tables
•Functions
•Constraints
•Schemas
•types.

It also stores all application-level objects in the database, including
•Logins
•Application-level agent jobs
•Persisted error messages
•Linked server information
•System settings

What are the Benefits of using Contained Database ?

Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.

What type of Authentication Modes are supported by Contained Database?

Contained Databases supports
•SQL Server Authentication
•Windows Based Authentication

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn’t exists in master’s syslogins tables and will not even show up when you list all SQL Users.

How we create a Contained Database ?

Prior to create a Contained database we need to enable "contained database authentication" property at SQL Server Level. This can be enabled using the following code.
-- Enable contained database authentication on the instance of SQL Server
-- This can done by A member of the sysadmin fixed server role
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
How to create Users in Contained Databases ?

A Contained database can be created as a normal user database, but with a property named "CONTAINMENT".

Following code, will create a database named "TEST" as contained database.
-- To create contained db you have to specify CONTAINMENT property
CREATE DATABASE TEST
CONTAINMENT = PARTIAL;
go
How to create a user inside a Contained Database ?

A user can be created as a normal user in contained database. The following code, will create a new user name "usr_TEST" inside a TEST Contained database.
USE TEST;
go
-- Create a contained SQL Server Authentication user
CREATE USER usr_TEST
WITH PASSWORD = 'TEST@123$';
go
How to connect to Contained database using a user which exists in Contained Database

While making a connection to SQL Server, we need to specify the [Contained Database name] as default database during the connection.



How The Authentication Process works in case of Contained Database ?

Authentication process can be understood using the following flowchart.


Thanks to :- http://sqlserver-training.com/what-is-contained-database-in-sql-server/-