An Introduction to Sql 11 (Code Name Denali) -Part IV (Contained Database in CTP 1)

Niladri_Biswas

4.75/5 (11 votes)

May 20, 2011

CPOL

11 min read

27130

In this article we will look into the basics of Contained Database that has been shipped with Sql 11 (Code Name: Denali) CTP 1

An Introduction to Sql 11 (Code Name Denali) -Part IV (Contained Database in CTP 1)

Table of Content

Introduction
Background
The Problem with the current (non contained) database
What is Contained Database?
Some terms we should know

Application boundary
Application Model
Contained
UnContained
Non-contained database
Fully contained database
Partially contained database
Contained user

A four step approach to create a Contained Database

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level
Step 2: Create a database and set its CONTAINMENT property to Partial
Step 3: Create a Contained User within the newly created contained database
Step 4: Login to the Contained database using the user that exists in Contained Database

Converting a Non Contained Database to a Contained Database
Back up a contained database
Restore up a contained database
Some more facts about contained database

Authentication Modes supported by Contained Database
Change in Create/Alter Database Statement

References
Conclusion

Introduction

One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). The CTP1 is available both in 32-bit and 64-bit versions. As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals. Part I describes about the features and enhancements in SSMS. Part II focuses on the new development and enhanced features from T-Sql perspective. Part III looks into the enhancements made from SSIS angle. In this series we will explore the Contained Database feature . We will focus on other features of Denali CTP-I in the subsequent series.

Background

In the last few years, Microsoft has brought many technologies under the developers' hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql Server 2011(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements. This article will give us an in-depth view about the contained database, what it is, its usage and many more stuffs.

The Problem with the current (non contained) database

Before going to describe what Contained Database is, let us see the reason for which it has come into existence.

Some of the problems with the current database are listed as under

Loss of Information during database movement or deployment

When we move the database from one Sql Server instance to another, then information such as login, job agent information cannot be shipped. This is because these information are application specific and henceforth resides inside the Sql Server instance. Recreating such task again on the new Sql server instance is a time consuming, error prone process.

Diversion from Application Development to Application Deployment

There may be hindrance while deploying the application in the server as there is a high possibility of environmental mismatch e.g. there may not permission about new login creation, command line facility like "xp_cmdshell" may be disable, database collation use by the application may not be same after deployment as it is initially determine by the server collation.

Security concern in Application Administration

It becomes difficult to administer and maintain a single database because the login and job agent information is available across the database instance, thereby, allowing the user to grant permission for the entire instance which yields unnecessary access to other database leading to security vulnerability.

What is Contained Database?

As the name suggests, it is a kind of data base that is self contained i.e. it carries all the database settings and metadata information needed to set up the database. It is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users. As it is independent of database instance, it ensures that database collations will not be an issue while deploying onto a different server.

A contained database, keeps all necessary information and objects in the database like Tables, Functions, Constraints, Schemas, types etc. It also stores all application-level objects in the database, such as Logins, Persisted error messages, Application-level agent jobs, System settings, Linked server information etc.

The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.

Some terms we should know

Application boundary

It is the boundary between the server instance and the application code

Application Model

Inside the application boundary it is the place where applications are developed and managed

Contained

It is a user entity that resides entirely within the application boundary.

Uncontained

It is a user entity that crosses the application boundary.

Non-contained database

Database whose containment is set to NONE.

Fully contained database

Database that does not allow any objects or functions to cross the application boundary.

Partially contained database

Database that allows features to cross the application boundary. It is available in CTP 1

Contained user

There are two types of users for contained databases.

(a) Contained database user with password which are authenticated by the database.

(b) Windows principals that can directly connect to the database and does not need any login in the master database.

A four step approach to create a Contained Database

By this time, I think we have some theoretical concept as what contained database is, why it came into picture. Now it's time to have some hands on in it. The below is a four step approach of creating a Contained database.

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level.

Step 2: Create a database and set its CONTAINMENT property to Partial.

Step 3: Create a Contained User within the newly created contained database.

Step 4: Login to the Contained database using the user that exists in Contained Database.

We will look into each and every steps in the below paras.

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level

1.Login to the SQL Server Management Studio of Sql 11 (Code name Denali) and from the Object Explorer, right-click on the Server instance and click "Properties"

2: Visit the "Advance" tab and from there set the "Enable Contained Databases" property, that comes under the "Containment" group section to TRUE.

The same can be achieved by using the below T-Sql script

--Enabled Advanced options
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

Step 2: Create a database and set its CONTAINMENT property to Partial

1.Create a new database by the name "TestContainedDB".

2.Right click on the "TestContainedDB" and click "Properties"

3.Visit the Options tab and select the Containment type as "Partial"

The same can be achieved by using the below T-Sql script

USE [master]
GO

CREATE DATABASE [TestContainedDB]
 CONTAINMENT = PARTIAL
 ON  PRIMARY 
( NAME = N'TestContainedDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\TestContainedDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestContainedDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\TestContainedDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [TestContainedDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [TestContainedDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [TestContainedDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [TestContainedDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [TestContainedDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [TestContainedDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [TestContainedDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [TestContainedDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [TestContainedDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [TestContainedDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [TestContainedDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [TestContainedDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [TestContainedDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [TestContainedDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [TestContainedDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [TestContainedDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [TestContainedDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [TestContainedDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [TestContainedDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [TestContainedDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [TestContainedDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [TestContainedDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [TestContainedDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [TestContainedDB] SET  READ_WRITE 
GO

ALTER DATABASE [TestContainedDB] SET RECOVERY FULL 
GO

ALTER DATABASE [TestContainedDB] SET  MULTI_USER 
GO

ALTER DATABASE [TestContainedDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [TestContainedDB] SET DB_CHAINING OFF 
GO

ALTER DATABASE [TestContainedDB] SET DEFAULT_FULLTEXT_LANGUAGE = 1033 
GO

ALTER DATABASE [TestContainedDB] SET DEFAULT_LANGUAGE = 1033 
GO

ALTER DATABASE [TestContainedDB] SET NESTED_TRIGGERS = ON 
GO

ALTER DATABASE [TestContainedDB] SET TRANSFORM_NOISE_WORDS = OFF 
GO

ALTER DATABASE [TestContainedDB] SET TWO_DIGIT_YEAR_CUTOFF = 2049 
GO

Step 3: Create a Contained User within the newly created contained database

1.Visit the "Users" node under the "Security" node of the "TestContainedDB".

2.Right click on the "Users" node and click on the "New User".

3.Create any user and password. Here the user name is : TestUser and the Password is : testuser

4.Check the "db_owner" checkbox from the Membership tab.

The same can be achieved by using the below T-Sql script

USE [TestContainedDB]
GO
CREATE USER [TestUser] 
WITH PASSWORD='testuser', 
DEFAULT_SCHEMA=[dbo]
GO

Once done, we can find that our user has been created

Step 4: Login to the Contained database using the user that exists in Contained Database

After the third step, let's log out of SSMS. Again login to SSMS as carry out the below steps

1.In the login name and password boxes, enter the login name and the password of the user created for the "TestContainedDB" i.e. User Name as "TestUser" and Password as "testuser".

2.Next, click on the "Options" button and visit "Connection Properties"

3.In the Connect to Database box, let us type in the name of the contained database which is "TestContainedDB" in our case

4.Click on the "Connect" button now and we are in our contained environment

Converting a Non Contained Database to a Contained Database

In this section we will look into how we can convert a Non Contained Database to a Contained Database.

Initial setup

Create a database say "NonContainedDB" by issuing the below script

USE [master]
GO

CREATE DATABASE [NonContainedDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'NonContainedDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\NonContainedDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'NonContainedDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\NonContainedDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NonContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [NonContainedDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [NonContainedDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [NonContainedDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [NonContainedDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [NonContainedDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [NonContainedDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [NonContainedDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [NonContainedDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [NonContainedDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [NonContainedDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [NonContainedDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [NonContainedDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [NonContainedDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [NonContainedDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [NonContainedDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [NonContainedDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [NonContainedDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [NonContainedDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [NonContainedDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [NonContainedDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [NonContainedDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [NonContainedDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [NonContainedDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [NonContainedDB] SET  READ_WRITE 
GO

ALTER DATABASE [NonContainedDB] SET RECOVERY FULL 
GO

ALTER DATABASE [NonContainedDB] SET  MULTI_USER 
GO

ALTER DATABASE [NonContainedDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [NonContainedDB] SET DB_CHAINING OFF 
GO

Next add a table say "tbl_Players" by issuing the below script

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
	PlayerID INT IDENTITY,
	PlayerName VARCHAR(15),
	BelongsTo VARCHAR(15),
	MatchPlayed INT,
	RunsMade INT,
	WicketsTaken INT,
	FeePerMatch NUMERIC(16,2)
)

--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

Also, add a stored procedure to the database by issuing the below script

If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the  stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
	Select 
		PlayerID
		,PlayerName
		, BelongsTo
		, MatchPlayed
		,RunsMade
		,WicketsTaken
		,FeePerMatch
	From
	tbl_Players
	Where PlayerId = @PlayerID
End

This means that, now we have a database "NonContainedDB" that has two database objects a table "tbl_Players" and a stored procedure "usp_SelectRecordsByPlayerName". At present this database is a non contained one. Our objective is to make this database to a contained one.

Step 1:

As a first step we will create a new server-level login and will create a user for that login for the "NonContainedDB". Let us issue the below script against the master database

--Create a login on the server
CREATE LOGIN NonContainedUser
WITH PASSWORD = 'somepassword@123'

--Create a "non-contained" users for the login on the server
USE NonContainedDB
GO
CREATE USER NonContainedUser FOR LOGIN NonContainedUser
GO

Step 2:

Now let us identify the non contained database objects for the database "NonContainedDB". For that reason, let us issue the below script

USE NonContainedDB
GO

SELECT 
	class_desc
	,feature_name
	,feature_type_name 
FROM sys.dm_db_uncontained_entities

The output is as under

We can ignore ROUTE. So there are two non contained database objects as highlighted.

For identifying the non-contained user in the database we can issue the below script

USE NonContainedDB
GO
SELECT dp.name 
FROM sys.database_principals dp 
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
  AND sp.is_disabled = 0

And it will give the below result

Step 3:

Right click on the "NonContainedDB" and click "Properties". Visit the Options tab and select the Containment type as "Partial".

Alternatively, we can achieve the same by issuing the below T-Sql script

USE master
GO
ALTER DATABASE NonContainedDB SET CONTAINMENT=PARTIAL;
GO

And after this issue the below script

USE NonContainedDB
GO
EXEC sp_migrate_user_to_contained @username = N'NonContainedUser', 
                                  @rename = N'keep_name', 
                                  @disable_login = N'disable_login'

The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.

Now let us again run the same query for identifying uncontained users in the database

USE NonContainedDB
GO
SELECT dp.name 
FROM sys.database_principals dp 
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
  AND sp.is_disabled = 0

And the result is as under

So we can figure out that, the "NonContainedUser" no longer appears. This means that it has been changed to contained user. Also the login has been disabled at the server level.

Step 4:

Let's log out of SSMS. Again login to SSMS. In the login name and password boxes, enter the login name and the password of the user created for the "TestContainedDB" i.e. User Name as "NonContainedUser" and Password as "somepassword@123". Next, click on the "Options" button and visit "Connection Properties". In the Connect to Database box, let us type in the name of the contained database which is "NonContainedDB" in our case. Click on the "Connect" button now and we are in our contained environment

Back up a contained database

We can take backup of a Contained Database in the same way we take for Uncontained Database. We can do this either through

a)Without T-Sql Script

1.Login to SQL 11 ("Denali") via the SSMS

2.In the object explorer, navigate to "TestContainedDB"

3.Right click, go to Tasks->Backup

b)With T-Sql Script

We can even take the database backup by issuing the below script

BACKUP DATABASE TestContainedDB
TO DISK='<File Path>\ TestContainedDB.bak'

Restore up a contained database

Like backup, we can restore the database either through

a)Without T-Sql Script

1.Login to SQL 11 ("Denali") via the SSMS

2.In the Object Explorer, navigate to the "Databases" node

3.Right-click, and go to Restore Database

b)With T-Sql Script

RESTORE DATABASE TestContainedDB
FROM DISK='<File Path>\TestContainedDB.bak'

But we may encounter the below error while we run the script

Msg 12824, Level 16, State 1, Line 1
The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

It is clear from the message that, we need to Enable "Contained Database Authentication" property on the SQL Server instance Level which is turned off by Default. Issuing the below script fixes the problem

--Enabled Advanced options
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

Some more facts about contained database

Authentication Modes supported by Contained Database

a)SQL Server Authentication

Windows Based Authentication

Change in Create/Alter Database Statement

The CREATE / ALTER DATABASE statement works differently in case of Contained Database. The statement

	Alter Database <Database Name>

does not work anymore in case of contained database. Instead, a new option called CURRENT has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.

Henceforth, the statement

	ALTER DATABASE CURRENT

Works for contained database.And

	Alter Database <Database Name>

Works for non contained database.

References

Understanding Contained Databases

Conclusion

In this article, we have seen what contained database is, why it came into existence, how it solved the issues created by non-contained database, how we can create a contained database, conversion of a non-contained database to a contained one etc. Hope this article has given the necessary idea to start working on contained database. We will explore more features of Denali CTP 1 in the subsequent articles.

Thanks for reading