Contained Databases is GooooD

Hi Guys,
Let's check out the contained database feature today. The contained database user's can be based on both sql and windows based. They need not be mapped to any login and the best part which I liked is that the application user will have absolutely no idea about the server it's running on.

Creating a Contained Database

To start with the Contained database you will have to first configure the Contained database option.

exec spconfigure 'contained database authentication',1
go
reconfigure
go

Now we will be ready to create a partial contained database (No Clue why they call it partially contained). So let's fire the create database command.

Create database Contained
containment =partial

This will create a database for us which will be contained. Now lets go ahead and create an object (i.e a table) in this database and put in some values.

USE [COntained]gocreate table conTab(name char(100))
insert into contab values ('tushar'), ('kusum'),('swati'), ('shashank')
go Now lets go ahead and create a windows user for this database.
USE [COntained]goCREATE USER [eapac\etuskanadm] WITH DEFAULT_SCHEMA=[dbo]goexec sp_addrolemember 'db_owner','eapac\etuskanadm'
go
 Now this user will be comtained in the database itself as it is not mapped to any login and this user will have no server level information. The below image shows how the connection to the Server/Database will look like. Here while connecting using ssms you have to addtionally go to the options in the ssms and there type the database name.



























You can also connect through command line you have to use the -d paramter in the connection to connect to the database.

sqlcmd -E -Slocalhost -dCOntained

Thanks,
Tushar Kanti