Skip to content

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.