Introduction

I'm a seasoned SQL Server architect and writing this article is a bit awkward to me nonetheless.
To do away with all the intimidation I decided that I should write and experience it myself.
I hope it becomes easier to you to when your done reading this material and doing the exercises.

Uncontained database


When we create a database by default some of the objects resides outside the database and are
stored in the system database therefore referred to as  uncontained. Logins among others for example
resides on the master database.



Figure 1. Uncontained Database.

Real Life Scenario

Let's say that you want to build an application that is sitting on top of SQL Server and it so happens that
You will have to deploy the application to 2000 different client. Lets say for example you are going to build  an
internet cafe timer  application and you will have to deploy it among disparate internet cafe across the
globe.

That's going to be a difficult task to do if you are going to deploy the rest of the system databases. Now
imagine if you can deploy the application database  alone. The answer to this kind of scenario is to implement
a contained database.

SQL Server 2012 and Database Containment

With SQL Server 2012 comes the ability to implement a 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."
 Technet
 

Figure 2. Contained Database

Implementing Partially Contained Database Step by Step

To implement a contained database we must follow the following steps at a high level:

  1. Enable Contained Database Authentication
  2. Create a Contained Database
  3. Create a user in the Contained Database
  4. Authenticate a user against the Contained Database

Step #1. Enable Contained Database authentication

To enable contained database authentication you must issue an sp_configure Command.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

But of course you can do that in SSMS.

Figure 3. Setting Contained authentication via SSMS

Step 2.  Create a Contained Database

 The next step is to create a contained database. Well, yes! you can create it with SSMS. See below.

Figure 4. Creating a contained Database thru SSMS

But  better yet you can  do it using TSQL as shown below

CREATE  DATABASE [DemocontainedDB]
CONTAINMENT=PARTIAL

Step 3. Create a User in the Contained Database

 

Let's create a user in the database. Lets script it so its faster. Somewhere in my mind though ask how is database
different from the rest. maybe will find out later. but for now lets run this script.


CREATE USER containedMe with password='verysecure',DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember'db_owner', 'containedMe'

Step 4. Create Sample Play with Data

Next step is to create play with data for testing

Step 5. Log-in to the contained database.

 

 

Sweet. I was able to log-in.



Honestly, I did run to some 18456.

I did run with some 18456 earlier but i just change the service login to Local system account.