Contained Databases.

Problem: What are contained databases, contained database users and how to create a contained database user. Where can we use a contained database.

Solution:

Contained Database:

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Contained databases have no external dependencies. The primary benefit of a contained database is that you can move it to another instance, or even to an appropriately configured cloud provider, and have the database work without requiring additional configuration. Contained databases do not use SQL logins but instead use contained users.

Contained Users:

A contained user is a database user who does not have a SQL Server login. Contained users can connect to the contained database by specifying credentials in the connection string.

Where to use:

A contained database is an option when You need to ensure that the application login is unable to access other production databases.

Example: In this example, I would like to show we can create a contained database and then create a contained user on the contained database.

Step 1: Configure contained database authentication.

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'contained database authentication', 1;

RECONFIGURE WITH OVERRIDE;

GO

OUTPUT:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'contained database authentication' changed from 0 to 1. Run the RECONFIGURE statement to install.

Step 2: Create contained database and create a contained user to connect to this database.

CREATE DATABASE Test_contained_db

CONTAINMENT = PARTIAL;

GO

USE Test_contained_db

GO

CREATE USER [ContainedDBUser] WITH PASSWORD=N'Test_Contained!',

 DEFAULT_LANGUAGE=[English],

 DEFAULT_SCHEMA=[dbo]

GO

Connecting to the contained database as below. 

 As we can see below the contained db user is only able to see the contained db with the login permissions whereas the sa user is able to see all the databases.