SQL Server Contained Databases for Database Refreshes

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


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.


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".

SQL Server Management Studio Server Properties to Enable Contained Databases

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
sp_configure 'contained database authentication',1

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.

New Database interface in SQL Server Management Studio to configure the containment type

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:

( NAME = N'Test', FILENAME = N'C:\tmp\Test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'Test_log', FILENAME = N'C:\tmp\Test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

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.

Enable Containment for an Existing Database using SQL Server Management Studio

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]

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.

Creating new Contained SQL Server Users in SQL Server Managment Studio

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]

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.

A user called TestUser1 without a corresponding SQL 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.

 TestUser2 is 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
    @username = N'TestUser2',
    @rename = N'keep_name',
    @disablelogin = N'do_not_disable_login' ;

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.

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.

SQL Server Management Studio Additional Connection Parameters

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

SSMS access with a contained database user
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell 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

Comments For This Article

Tuesday, January 7, 2014 - 1:41:39 AM - Kartar Rana Back To Top (27973)

Great Article as this helped me to clearly understand Contained Databases.

But i would like to point out the issues that i had while going through the article.

In Step 4, you have created a "SQL User without Login" which should be "SQL User with Password" because creating a SQL user without a login is just like creating an orphaned login with no purpose at all. The user would not be able to login to the server and there is no password defined for the same. The screenshot under the step 4 is taken from a database that is not "Contained" that is the reason "SQL User with Password" does not appear in the dropdown.

Tuesday, March 12, 2013 - 3:05:49 PM - Chad Churchwell Back To Top (22756)

Mladen -

sysadmin would be a server level permisison and not a database level permission, which is the intent of contained users, so we are talking about logins and not users at that point.    When a user becomes a contained user all ties to the login are dropped, the login can still exist, but there is not a tie of that database user to the server login.  I would think you could make the server login sysadmin without affecting the contained user but I have not tested that.  I am not familiar with a system proc to "undo" the user containment

Tuesday, March 12, 2013 - 7:28:28 AM - Mladen Andrijasevic Back To Top (22731)

Suppose we create a contained user with  CREATE USER [ContainedUser] WITH PASSWORD=N'AÔ=)@•#~R/$¤mòsñ^õø”^ÐU¡/', DEFAULT_SCHEMA=[dbo]

What if  we changed our mind want to create non-contained user from this already existing contained user?  Is it possible to have a procedure that does the reverse of sp_migrate_user_to_contained? For instance, we  need a user to have sysadmin authority and  it  is a contained user  and that by the very definition cannot have server level  authority.  So is  there a way of doing this  ,   apart from dropping the user ContainedUser  and then recreating the previously contained user with CREATE USER [ContainedUser] FOR LOGIN [ContainedUser] WITH DEFAULT_SCHEMA=[ContainedUser]   







Thursday, December 13, 2012 - 1:56:06 PM - M Azim Back To Top (20946)

Very well explained. Thanks for sharing the Tip

Monday, October 29, 2012 - 7:52:26 PM - Chad Churchwell Back To Top (20137)


Partial is the name they use for this first rollout of contained databases, I think (my opinion) that they will add more functionality in the future to make databases fully contained but as of right now it is just users.

Under Step 4 in the above article it shows the login screen for creating users without a login

For your last bullet point, that is a great catch, you should choose "SQL User with password" and provide a password and it will create just the user without the login.  You would use "SQL User without login" if you were using windows authentication.  Thanks for the catch on that

Monday, October 29, 2012 - 7:39:15 PM - Chad Churchwell Back To Top (20136)

Richard, at first thought it should work for MS Dynamics GP as the connection strings don't change, just where the meta data resides within SQL Server.  I would not think any GP code would need to be changed but would confirm with MS and of course test in a non-prod environment.  Another thing you would need to verify is that GP users are indeed SQL users and not data stored in like a "Users" table that may be causing your headache when moving to a new server

Monday, October 29, 2012 - 2:44:02 PM - Richard L. Whaley Back To Top (20134)

Excellent presentation.

On a scale of 1-10, my SQL experience is about a 5.  I help implement MS Dynamics GP applications that use SQL.  GP automatically creates users at both the system and db level.  We have a horrible problem when we move a db to an upgraded server (different physical box with upgraded SQL) to get the existing users on board. 

Will this work for us?  Keep in mind we cannot (or do not want to) modify the GP code.


Monday, October 29, 2012 - 1:18:56 PM - sqlfriend Back To Top (20131)

Thanks for the step by step explanation, this is very helpful.

A couple questions:

  1. What does partial mean exactly when you create the contained database and make the settings?
  2. In SSMS, when creating the new users, in the user type dropdown menu, which are options for creating users that does not need the login (self contained). Is the SQL user without login is the only option?
  3. and when you say in the article quote: "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." I tried to create the SQL user without login option, I don't see there is anywhere in the window asked me to put the password. Do I have to create a login first?


Monday, October 29, 2012 - 11:41:46 AM - Chad Churchwell Back To Top (20129)

Thanks Rick, glad you found it useful

Monday, October 29, 2012 - 9:11:29 AM - Rick Dobson Back To Top (20126)

It was a very clearly presented explanation of contained databases and their uses.  Thanks for the tip.

get free sql tips
agree to terms