Contained Database is a new feature of SQL Server 2012.
As per MS Contained Database is defined as:
“A Contained Database is database that is isolated from
other databases and from the instance of SQL Server that hosts the Database. ”
In Simple words:
Now Database can store the User information/Credentials in
Database it self instead of Instance level.
This feature is very useful when moving the database from
one server to another server.
The containment setting of a database can be NONE,PARTIAL or
FULL.
SQL Server 2012 supports only NONE and PARTIAL Containment settings.
Requirements of Contained Databases:
- Contained Databases need to enabled on instance
- Contained Database need to be specified while connecting
thru SSMS.
First we need to enable the contained databases at instance
level. That we can do it in two ways.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Second way is to do it from SSMS.
From ssms right
click on sqlserver instance then Click
Adavnced Page then set
the Enable Contained Database property to TRUE.
Once its enabled at instance level, we can create the
database by stating Containment
When creating a database Database from SSMS, we need to
mention the containment type property value to “Partial” as shown in below image .
Once Database is created with Partial containment we need to
create users in it. These users authenticated by Database rather than Instance.
USE [Contained_Demo]
GO
CREATE USER [User_Demo] WITH PASSWORD=N'demo1234',
DEFAULT_SCHEMA=[dbo]
GO
Now add the user to
db_owner role
USE [Contained_Demo]
GO
ALTER ROLE [db_owner] ADD MEMBER [User_Demo]
let’s
try to connect to the database using the user account created above, which is
expected to be authenticated by the database:
Ohh..its throws an
error. As mentioned in the above we need to mention the Database name in
connection string.
In connection properties
we need to mention the Database name
When sucessfully
authenticated by DB, User will see only DB to which user has access..
Advantages of Contained
Database:
- Easy to move database
from one server to another.
- Authentication is done
by DB rather than Instance
- contained Database users
can be windows/Sql authenticated.
Limitations:
- Partially Contained
databases cannot use CDC,CT,Replication.
