Understanding Contained Database in SQL Server 2012

SQL Server 2012 supports contained databases and partially contained databases, which provide a high degree of isolation from the database engine instance but are not fully contained. The contained database becomes highly portable that you can migrate it to any instance or even in SQL Azure.

In SQL Server 2012, the implementation of partially contained database does not support the following

  • Change Tracking
  • Change Data Capture
  • Binding changes resulting from collation changes
  • Replication

You can enable contained databases at the server level by opening the server properties in SSMS or by running the following statements

USE [master]
GO

–Turn on contained database at server level
sp_configure ‘show advanced’, 1
RECONFIGURE
GO
sp_configure ‘contained database authentication’, 1
RECONFIGURE
GO

Then after everything is OK, proceed to the database which you wish to enable containment. Right click and open the database properties. You can also use the ALTER DATABASE statement with the SET CONTAINMENT option to configure.

Now to test and play around with the database containment feature, run the following scripts below;

–Create containted database
CREATE DATABASE [BlogDemoDBContained]
CONTAINMENT = PARTIAL
GO

–Create SQL Login contained user in database
USE [BlogDemoDBContained]
GO

CREATE USER democontaineduser WITH PASSWORD=N’Passw0rd’, DEFAULT_SCHEMA=[dbo]
GO

Then disconnect your current query window. Try to connect to database via the GUI and but click on OPTIONS to open window same as below;

Containment Login

Make sure to select the contained database in the Connect to database drop down list. Enter the credentials of your demo contained user we created above and viola you should be able to connect to the instance showing only the contained database. There you go.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: