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
- An element that exists entirely in the database boundary.
- 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.
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
- It is quite easy to move the database from one server to another, as we will not have orphaned user issues
- Metadata is stored on contained databases so it is easier and more portable.
- We can have both SQL Server and Windows authentication for contained DB users.
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.