Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Contained Databases for Database Refreshes


By:   |   Last Updated: 2012-10-29   |   Comments (10)   |   Related Tips: More > 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".

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

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:

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.

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

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

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

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


Last Updated: 2012-10-29


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 07, 2014 - 1:41:39 AM - Kartar Rana Back To Top

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

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

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

Very well explained. Thanks for sharing the Tip


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

SQLFRIEND -

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

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

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

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

Thanks Rick, glad you found it useful


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

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


Learn more about SQL Server tools