Contained Database

 

First introduced in SQL 2012 and its supported now.

What it is

                Contained databases have no dependencies on server-level metadata and settings. User can access a Contained Database without authenticated a login at DB instance level. This helps to isolate the database from database engine and make it possible to easily move the DB from one SQL instance to another. In simple word, user get authenticated at DB level and DB can easily portable to other server without mapping login with DB.

Step by step implementation

Step1. Enable 'contained database authentication' on the DB instance.

exec sp_configure 'contained database authentication',1

reconfigure with override

Step2. Enable containment=partial on DB.

create database <DB_Name> containment=partial

If DB already exists.

alter database <DB_Name> set containment=partial

Step3. Create DB user with password.

use <DB_Name>

create user <user_name> with password =N'*******', default_schema=your_schema_name

Step4. Grant permission. I’m giving select permission here.

use <DB_Name>

GRANT SELECT ON <table_name> TO <user_name> WITH GRANT OPTION  AS your_schema_name

 

In my case...

Connecting DB with the user using SSMS

Use of Contained DB

·         Authentication

·         Use with AG

·         In Dev environment

·         Make a day for DBA moving here and there.

Problem with Contained DB

·         Replication is not supported

·         Change data capture does not work

·         Change Tracking does not work out

·         Connection string change

·         Cross DB query does work

·         Difficult to manage if the server is having password policy enabled