SQL Server Contained Database

SQL Server Contained Database, as the name implies, is a self-contained database that has its own database setting, configurations, and metadata and does not depend on the SQL Server instance

Advantages of a contained database

Having this feature allows the database to bypass having a SQL login to access the database and only depends upon the creation of a SQL User to access the database

Permits the database to be moved to another server without creating ‘security issues’ and orphan SQL Logins

A fully contained database has no configuration dependencies on the instance of the SQL Server (SQL Server 2016)

Partially contained databases make it easier to separate a database from the instance of SQL Server and other databases (SQL Server 2014)

By reducing the ties to the instance of SQL Server, partially contained databases can be useful during failover when you use AlwaysOn Availability Groups.

Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an AlwaysOn solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit.

Partially contained databases cannot use replication, change data capture

Steps for creating a contained database via TSQL and GUI

  • Before a contained database can be created, it must be enabled using the sp_configure sproc
  • Create a Contained Database using TSQL
  • Create an SQL Server User using TSQL

Terminology:

  • Contained
    • An element that exists entirely in the database boundary.
  • Uncontained
    • An element that crosses the database boundary.
  • Non-contained database
    • A database that has containment set to NONE. All databases in versions earlier than SQL Server 2012 are non-contained. By default, all SQL Server 2012 and later databases have a containment set to NONE.

/*
The following demonstration will show that a regular database
which has SQL users and SQL logins DO NOT migrate to another instance
of a database, but rather become an SQL login orphan. But a contained database
when moved, retains the SQL user and SQL login information so as to prevent orphans
*/

--create test db

CREATE DATABASE PROD
GO

--create sql login and sql user for prod db

USE [master]
GO


CREATE LOGIN [ANDY] WITH PASSWORD=N'password#123', 
DEFAULT_DATABASE=[PROD], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [PROD]
GO

CREATE USER [ANDY] FOR LOGIN [ANDY]
GO

USE [PROD]
GO

ALTER ROLE [db_owner] ADD MEMBER [ANDY]
GO

--backup prod database with the SQL login Andy.  Change connection in this query pane to run the restore

Use Master
go

BACKUP DATABASE [PROD] 
TO  DISK = N'C:\ProdBackup\prod.bak' 
WITH  COPY_ONLY, 
NOFORMAT, 
NOINIT,  
NAME = N'PROD-Full Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10
GO


--Restore database Prod on different instance.  Must use a differnt connection to the Dev instance
--Notice that the SQL databse user ANDY moved, but the SQL Login DID NOT copy over!!  This is a SQL orphan

USE [master]

RESTORE DATABASE [PROD] 
FROM  DISK = N'C:\ProdBackup\prod.bak' 
WITH  FILE = 1,  
MOVE N'PROD' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\PROD.mdf',  
MOVE N'PROD_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\PROD_log.ldf',  
NOUNLOAD,  STATS = 5
GO

--Find SQL login orphans.  Run this script against the Dev database to find orphans. Change connection in this query pane

USE PROD
EXEC sp_change_users_login 'Report';

--this will fix by mapping the SQL user to the SQL Login

EXEC sp_change_users_login 'Auto_Fix', 'ANDY', NULL, 'PASSWORD'; 

---extra work needed in a non contained database to resole issues of orphans and security!!

use master
go
drop database prod 

--drop sql login in both databases

USE [master]
GO

DROP LOGIN [ANDY]
GO

------------------------------------------------------------------------

--this issue of orphans does not exist in contained databases as 
--each database has it's own meta data about security and configuration

--DROP DATABASE ContainDB
--CHANGE THE AUTHENTICATION MODE TO MIXED

--USE [master]
--GO
--EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
--N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
--GO

----RESTART SQL SERVER

----CHANGE THE AUTHENTICATION MODE TO WINDOWS

--USE [master]
--GO
--EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
--N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
--GO

--RESTART SQL SERVER

-----------------------------------------------------------------------------------------

--Configure a contained database via sp_configure

sp_configure


--set option on

sp_configure 'contained database authentication', 1
go
reconfigure
go

sp_configure

--set option off

sp_configure 'contained database authentication', 0
go
reconfigure
go

sp_configure


--CREATE A CONTAINED DATABASE.  SAME AS CREATING A REGUALAR DATABASE, ONLY THIS TIME ' CONTAINMENT = PARTIAL' ADDEDD

CREATE DATABASE [ContainDB]
 CONTAINMENT = PARTIAL       --<< ADDITIONAL COMMAND TO CREATE A CONTAINED DATABASE
 ON  PRIMARY 
( NAME = N'ContainDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ContainDB.mdf' , 
SIZE = 4096KB , 
FILEGROWTH = 1024KB )

 LOG ON 
( NAME = N'ContainDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ContainDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%)
GO


--Create a SQL User to access the contained database.  Note NO SQL LOGIN!!!

USE [ContainDB]
GO

CREATE USER [Jack] WITH PASSWORD=N'password'
GO

USE [ContainDB]
GO

ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [Jack]
GO


--Information about uncontained objects or features.  We see that Jack is a SQL USER and
--has database authentication type rather than NONE - which is a reugular database

use ContainDB
go

Select name, type_desc,authentication_type_desc
from sys.database_principals

--BACKUP THE DATABASE CONTAINDB

Use Master
go

BACKUP DATABASE [CONTAINDB] 
TO  DISK = N'C:\ProdBackup\CONTAINDB.bak' 
WITH  COPY_ONLY, 
NOFORMAT, 
NOINIT,  
NAME = N'CONTAINDB-Full Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10
GO


--Restore database Prod on different instance.  Must use a differnt connection to the Dev instance
--Notice that the SQL databse user ANDY moved, but the SQL Login DID NOT copy over!!  This is a SQL orphan

USE [master]

GO
RESTORE DATABASE [CONTAINDB] 
FROM  DISK = N'C:\ProdBackup\CONTAINDB.bak' 
WITH  FILE = 1,  
MOVE N'CONTAINDB' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\CONTAINDB.mdf',  
MOVE N'CONTAINDB_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\CONTAINDB_log.ldf',  
NOUNLOAD,  STATS = 5
GO

--Find SQL login orphans.  Run this script against the Dev database to find orphans. Change connection in this query pane

USE CONTAINDB
EXEC sp_change_users_login 'Report';

--NO ORPHASN FOUND.  JACK MOVED WITH THE RESTORE TO THE NEW SERVER

---EXTRA WORK PREVENTED

Contained databases feature is available at instance level and it is not enabled by default.

To enable it, right click on server ➜ properties, go to Advanced, and enable the Enabled Contained Databases option

Alternatively, we can use a sp_configure system stored procedure to enable contained databases on the instance, as per below query:

 EXEC sp_configure ‘show advanced’, 1GORECONFIGUREGOEXEC sp_configure ‘contained database authentication’, 1GORECONFIGUREGO 

Creating a new contained database

If we want to create a new database as a contained database, we have to make containment type as Partial in the Options page.

SQL Server Contained Database

If we script out the create database, we can find out the query to create it using t-SQL as below:

 CREATE DATABASE [TestContainedDB] CONTAINMENT = PARTIAL ON  PRIMARY ( NAME = N’TestContainedDB’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestContainedDB.mdf’ , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’TestContainedDB_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestContainedDB_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%)GO 

Once the database is created, we can verify it using the sys.databases

 select containment,name from sys.databases where name=’TestContainedDB’ 

If the containment is not enabled in the databases, it will return 0, else 1, so in our case it should return 1 for TestContainedDB as shown:

Once the contained database is created, we need to create a new database user.

For this, we need to expand contained databases and go to security -> Create new database user and create new user type as SQL user with password, provide default schema, appropriate permissions required, as shown below:

The script below can be used to create the user by t-sql

 USE [TestContainedDB]GOCREATE USER [TestUser] WITH PASSWORD=N’test’, DEFAULT_SCHEMA=[dbo]Go 

So here we need not to create the Login before creating the user, we will directly create the user with appropriate password tagged.

Note: we cannot create the user in a normal database, which is not contained. If tried, we will get the below error

Msg 33233, Level 16, State 1, Line 1 You can only create a user with a password in a contained database.

How to connect to the contained DB using SQL Server Management Studio

Normally, to connect with SQL database instance we used to provide instance name, authentication method (windows\SQL) and, if SQL, username and password.

But to connect contained database we also need to specify contained DB name in the connection parameter.

If we try to connect contained database with contained user and password without specifying database name, we will get the error as:

So, in the connection properties we need to specify contained database name as shown below:

Once connected through SQL Server Management Studio, we can see the database user has access to:

Converting existing database to the contained database

If want to convert existing database into contained database, run the command below:

 ALTER DATABASE [Database_name] SET CONTAINMENT = PARTIAL WITH NO_WAITGO 

After converting the database we need to take care of the existing logins as well. For this, we need to move the users or migrate the users using the system procedure. We need to use sp_migrate_users_to_contained with below parameters.

  • @username = SQL Server authenticated user.
  • @rename = If login and username differ, this tells whether to keep the user name or take the login name.
  • @disablelogin = This parameter will disable to server login in the master database, if we want to

Suppose we have the user as TestMovecontained, so query for moving the user will be:

 sp_migrate_user_to_contained    @username = ‘TestMovecontained’,  –Userame to be specified here    @rename = N’keep_name’,    @disablelogin = N’do_not_disable_login’ ;GO 

Working on a contained database

As a contained user is created at database level, we cannot do any activity we require instance permissions like backup \ restore.

As visible below, we are not getting option of backup \ Restore database here but we can take the database Offline.

Listing out logins that are of contained user type

We can use the system view sys.database_principals view in the database to see which users are listed as contained users using the below query

 SELECT name,type_desc,authentication_type_descFROM sys.database_principals WHERE authentication_type =2 

Benefits of using contained databases

  1. It is quite easy to move the database from one server to another, as we will not have orphaned user issues
  2. Metadata is stored on contained databases so it is easier and more portable.
  3. We can have both SQL Server and Windows authentication for contained DB users.

Disadvantages

Partially contained databases cannot use features like replication, change data capture, change tracking, schema-bound objects that depend on built-in functions with collation changes.