SQL Server Contained Databases for Database Refreshes

By: Chad Churchwell   |   Comments (10)   |   Related: > Contained Databases

Problem

Often times we are tasked with moving databases from the production environment to a lower level environment such as QA or DEV.  One typical struggle is getting the logins correct and aligned with users within the database. Often after a restore you are left with orphaned users that do not have a corresponding server login, or misaligned internal SID's that you have to fix using the sp_change_users_login system stored procedure. Often this process takes multiple rounds to get everything correct and working again as users report "we are getting a login error".  Check out this tip to learn how to prevent this problem.

Solution

With the introduction of SQL Server 2012 a new technology has been introduced known as "Contained Databases". This enables a user in a database to exist without a corresponding login on the server. For security this makes the database more portable in the event you are tasked with migrating servers, or restoring lower level environments. As of right now, only partial containment is supported and that includes users. This makes the database independent of the instance. Please note since SQL Server Agent Jobs are stored in MSDB and not the user database, they are not portable as well as other server objects such as Linked Servers, etc. In this tip I will show you how to enable containment in your databases.

1. Enable SQL Server Contained Databases - Server Setting

First you have to enable containment at the instance level before you can apply containment at the database level. This can be done using the SQL Server Management Studio or T-SQL commands.

Using SQL Server Management Studio to Enable Contained Databases

In SQL Server Management Studio, right click on the instance name and select the "Properties" option.  Once the Server Properties interface loads, choose the "Advanced" option in the left navigation then set the "Enable Contained Databases" to "true".

T-SQL Commands to Enable SQL Server Contained Databases

Since this is a server configuration this also can be set using the sp_configure system stored procedure. The code below enables SQL Server Contained Databases on your instance.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication',1
GO
RECONFIGURE WITH OVERRIDE
GO

At this point your instance is configured to allow database containment. Next we will go through creating SQL Server Contained Databases with containment.

2. Creating SQL Server Contained Databases

To create a new database that has containment included, you can use the "CONTAINMENT = PARTIAL" option in the create database command or in SQL Server Management Studio.  Let's cover both options.

Create a SQL Server Contained Database in SQL Server Management Studio

In SSMS if you right click the "Databases" folder and select the "New Database" option.  This will load the New Database interface.  On this interface go to the "Options" tab and for the "Containment" dropdown select the "Partial" option.

T-SQL Commands to Create a SQL Server Contained Database

Using the CREATE DATABASE command in T-SQL, databases can be created with partial containment. Check out the following code:

CREATE DATABASE [Test]
 CONTAINMENT = PARTIAL
 ON  PRIMARY 
( NAME = N'Test', FILENAME = N'C:\tmp\Test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'C:\tmp\Test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

3. Enable Containment On Existing SQL Server Databases

If you have an existing database that is created you can enable containment using SQL Server Management Studio and T-SQL. If there are existing users in that database, I will cover how to migrate these to be a self contained user later in this tip.

Enable Containment for an Existing Database using SQL Server Management Studio

To enable containment for an existing database, right click on the database and select the "Properties" option which will load the Database Properties interface.  In this interface select the "Options" menu and for the "Containment Type" option select  "Partial" from the drop down list.

T-SQL Commands to Enable Containment for an Existing Database

Using T-SQL an existing database can be converted to partial containment using the ALTER DATABASE command as shown below.

USE [master]
GO
ALTER DATABASE [Test] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

Now that you have either a new database or have enabled containment on an existing database, you will need to either create new users or convert existing users to a contained user. I will cover both of these concepts in the next part of this tip.

4. Creating New Contained SQL Server Users

Creating contained users can be completed using SQL Server Management Studio or T-SQL commands.  Let's check out both options.

Creating new Contained SQL Server Users in SQL Server Management Studio

To create a new user in SQL Server Management Studio, expand the "Database" and "Security" folders, then right click "Users" and select "New User". The Database User - New interface should load and select "SQL user without login" as the User Type to create a database contained user.

T-SQL Commands to Create a new Contained SQL Server User

Using T-SQL, contained SQL Server users can be created using the "WITHOUT LOGIN" option.

USE [Test]
GO
CREATE USER [TestUser1] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO

At this point, your database can be backed up and restored to another SQL Server instance and users can connect using the login and password that you provided when creating the user. As you can see in the screen shot below there is a user called TestUser1 without a corresponding server login.

5. Migrating Existing SQL Server Users to a Contained User

If you have an existing database that you have recently converted to a SQL Server contained database, you can migrate an existing user to a contained user and the login can be dropped with some caution.  In this circumstance, make sure the login is not used for any other databases, SQL Server Agent Jobs, etc.  To the best of my knowledge an existing user can only be converted to a contained user using T-SQL commands.

Below is a screenshot where I have created "TestUser2" as a normal user and you can see it has both a server login as well as a database user.

Below is the T-SQL code used to migrate an existing user to a contained user.

USE Test
GO
sp_migrate_user_to_contained
    @username = N'TestUser2',
    @rename = N'keep_name',
    @disablelogin = N'do_not_disable_login' ;
GO

Code explanation: 

• @username = user that is mapped to a SQL Server authenticated login.
• @rename = If login and username differ, this tells whether to keep the user name or take the login name.
• @disablelogin = This will disable to server login in the master database if desired.
• For more information reference - sp_migrate_user_to_contained (MSDN)

At this time, I will drop the SQL Server login and you can see the user still exists.

As a last step I want to show you how to connect using the TestUser2 contained database user.  When using SSMS, you will have to specify the database name which is accomplished by clicking on the "Options >>" button and then specifying  "Additional Connection Parameters" i.e. Initial Catalog = < Database Name > as shown below.

Once you are connected in SQL Server Management Studio, you can only see your database and objects based on the associated permissions.

Next Steps

• Give SQL Server Contained Databases a try in your development environment and discuss with the development team if this will work for your organization.
• Additional resources:
  • Contained Databases
  • Migrate to a Partially Contained Database

About the author

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips