Contained Database

by Amol 12. March 2011 03:56

What is contained database and how to create it is explained in earlier post Using Contained Database

Here is SQL query to list out contained database users who can connect to it. Any user like windows, group user or SQL Server user contained in database can connect to contained database

FROM db_Contained.sys.database_principals 
WHERE authentication_type IN (2, 3)

Tags: ,

SQL Server Denali

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

Contained Database - SQL Server 2011 - Denali

by Amol 3. March 2011 10:53

One of the best feature available in SQL Server 2011 - Denali is Contained Database. As name indicates, it includes all the required meta data and database settings and it doesnt have any dependencies on SQL Server Database instance where database is hosted. That is why user can connect to database without authenticating at database engine level and contained database can easily move from one SQL Server instance to another.

Contained Database Model:

  • Application boundary
  • Contained
  • Uncontained
  • Non-contained database
  • Fully contained database
  • Partially contained database
  • Contained user

More information about Contained Databases is available on MSDN

Benefits of Contained Databases

Contained Databases Example

Tags: , ,

SQL Server Denali

Tag cloud