Share via

SQL Server 2012 Contained Database.

  • Article

Introduction

As a seasoned SQL Server architect writing this article is a bit awkward nonetheless.
To do away with all the intimidation we decided that we should write and experience it.
We 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 reside 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 we want to build an application that is sitting on top of SQL Server and it so happens that
We will have to deploy the application to 2000 different clients. Let's say for example we are going to build an
internet cafe timer application and we will have to deploy it among disparate internet cafe across the globe.

That's going to be a difficult task to do if we are going to deploy the rest of the system databases. Now
imagine if we 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 we 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! we can create it with SSMS. See below.

*Figure 4. Creating a contained Database thru SSMS
*
But better yet we 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. Let's script it so its faster. Somewhere in my mind though ask how is database
different from the rest. Maybe we will find out later but for now let's 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

 

 

Was able to log-in

We did run some 18456

We did run with some 18456 earlier but just change the service login to the local system account.