Using Contained Database

by Amol 5. March 2011 19:41

Why do we need Contained Database? Lets take an example, we develop application and database on our development machine. Now its time to deploy it on production. There are couple of ways to do it. One, generate database object script and create the database. Second, backup the database and restore it on production. These steps will create database on production but it wont create any dependant server level objects for example logins. With help of database database backup user will user will get created on production but it will be an orphand user. You have to map a login for the user. 

To avoid such ambiguities, Contained Database comes in picture. While creating database SQL Server Denali will gives you option of setting its containment type, which creates Contained objects defined at server level within database itself. Currently only containment option "Partial" is available in SQL Server Denali CTP 1. "Full" containment is not yet enabled in CTP 1.

Let's see how to create Contained Database. First you have enable contained databases server level option. To Enable contained databases open Server 

Properties as shown in figure. By default Contained Database property is False. To work on it make it True.


Or secondly you can enable this property by using T-SQL as well. 

sp_configure 'show advanced options', 1
sp_configure 'contained database authentication', 1

Once you enabled contained databases, create contained database using create database statement shown in below.


Lets create user in TestContainedDB. 

USE [TestContainedDB]
CREATE USER [TestUser] WITH PASSWORD = ‘password123’

If you notice, earlier we required valid server login to create database user. But here we do not require to create SQL Server login for the database user. So dependency between database user and server login has been removed here. Means it all required objects containance in Contained Database.

This is useful when you need to move database from one SQL Server instance to another. You do not have to map the database users to login. 

Tags: , , ,

SQL Server Denali

Add comment

  Country flag
  • Comment
  • Preview

Tag cloud