SQL Server Portal

Contained databases are isolated / independent databases from the other databases and from the instance it is hosted on.

Before in SQL Server 2012, SQL server maintained user authentication at server level. So once the user logged in either via Windows or SQL Authentication, user could access server objects.

Currently in SQL Server 2012, we have a concept of “partial contained databases” meaning it allows to access few elements from outside its boundary as well. Now you can directly access the database with the contained database user, you don’t need the server authentication for partial contained databases.

This feature is very handy especially when it comes to database authentication or to avoid server authentication. The administrator can directly assign the user to the contained database.

It is also important for “Always On High Availability” because once an SQL server instance is down the user can easily connect on other instance database as the database is not dependent on instance.

Let’s proceed step by step.

Step 1:
In the first step, we must activate the ‘contained database authentication’ on the instance.

Using Script

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

Using SSMS
Right click on Instance \ Properties \ Advance. You can find a property on top namely “Enabled Contained Databases”. By default it is “False” you need to change it to “True”.

Step 2 :

In this step, we need to create or alter a database to make it contained database.

Using Script

ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL WITH NO_WAIT
--OR
CREATE DATABASE [Contained_DB]
CONTAINMENT = PARTIAL;

After enabling partial contained in the database, we can execute this query to view that it is enabled or not.

Select containment,[containment_desc]   from sys.databases Where [name]='AdventureWorks2012'

Step 3:
In this step, we need to create a user having “contained database” rights (Rights can vary from reader to owner)
Using Script

Use Contained_DB
GO
CREATE USER imran
WITH PASSWORD = 'imran1234';

ALTER ROLE [db_owner] ADD MEMBER [imran]

Using SSMS
Right click on “contained database” \ Security \users\ New User …

Step 4:
Now, we have already created a user inside the “contained database”. In this step, we need to log in from this user.

Once you are in the log in screen, enter the credential but “don’t click on Connect”. Click on Options >> connection properties and select the “Contained_DB” from the “Connect to database” option after that you can click on connect.

Step 5:
Now, you are logged in successfully. Lets try to create one table and access it.

Use Contained_DB
GO
Create Table [Test_Table]
([SNo] int,
[Designation] varchar(50))

GO

Insert into [Test_Table] Values (1,'Director'),(2,'Manager'),(3,'Staff')
GO
Use Contained_DB
Select * from [Test_Table]

And you can create / view the table successfully.

Conclusion :

This is a very handy and important feature from the perspective of database migration, “Always On High Availability”, Server Authentication. But it also doesn’t allow us to do the replication, change data capture and change tracking.

Reference : MSDN

Read Full Post »