Contained database:
Connect to SQL Server 2012 Instance using SQL Server Management Studio and then right click the SQL Server Instance and choose Properties from the drop down list.
Step 2.
In Server Properties choose Advanced Page and set the value as True for Enable Contained Databases as highlighted in the below snippet and then click OK to save the changes.
Step 1.
The code will appear as below:
These two settings together will enable containment on a database, and then you can work with contained users.
–Create a contained database user:
CREATE USER [Anurag]
WITH PASSWORD=‘clover@123’,
DEFAULT_SCHEMA=[dbo]
GO
–Give the newly created user permissions to access the database:
EXEC sp_addrolemember ‘db_owner’, ‘Anurag’
GO
Contained Databases is a new feature which was initially introduced in SQL Server 2012. A contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created.
Contained databases have no dependencies on server-level metadata and settings. These databases are fully portable, which makes it easier for a DBA to move databases between instances without having to deal with issues like orphaned users.
Contained Database Authentication is not enabled by default on the SQL Server instance. First you need to enable it at the instance level before you can create a database with containment or a database that can authenticate users.
How to Configure Contained Database Feature in SQL Server Using T-SQL Code
Execute the below T-SQL to configure contained database feature in SQL Server at instance level.USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GOHow to Configure Contained Database Feature in SQL Server Using SSMS
Step 1.Connect to SQL Server 2012 Instance using SQL Server Management Studio and then right click the SQL Server Instance and choose Properties from the drop down list.
Step 2.
In Server Properties choose Advanced Page and set the value as True for Enable Contained Databases as highlighted in the below snippet and then click OK to save the changes.
Database Containment
The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab. You have “None” and “Partial” available, and clicking Partial will enable containment in 2012.How to Configure Database Containment Feature in SQL Server Using TSQL
The code will appear as below:
USE [master]You can also set this value when you create a database:
GO
ALTER DATABASE [ContainedDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
-- Create db with containmentThat’s it.
CREATE DATABASE ContainedDBCONTAINMENT = PARTIAL
How to Configure Database Containment Feature in SQL Server Using SSMS
These two settings together will enable containment on a database, and then you can work with contained users.
–Create a contained database user:
CREATE USER [Anurag]
WITH PASSWORD=‘clover@123’,
DEFAULT_SCHEMA=[dbo]
GO
–Give the newly created user permissions to access the database:
EXEC sp_addrolemember ‘db_owner’, ‘Anurag’
GO
Connect with created orphaned user & specify database name ContainedDB in Connection Properties:
You will then be connected to only the contained database. You should not be able to see any of the other databases or instance features.