Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

SQL Server 2012 Contained Database Feature


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | More > Contained Databases

Problem

While looking through the new features and improvements in SQL Server 2012, we found a potentially interesting feature called Contained Databases. A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another. In this tip we will take a look at how to configure and use this feature of SQL Server 2012.

Solution

Let us go through an example to configure a Contained Database in SQL Server 2012. Below are the steps to configure and use this feature.

1. Enable Contained Database Feature in SQL Server 2012 Instance Level
2. Create a Contained Database in SQL Server 2012
3. Create an SQL Server User to Access the Contained Database
4. Login and Verify the User Permission on the Contained Database


Enable Contained Database Feature in SQL Server 2012 Instance Level

Execute the T-SQL code below to enable the Contained Database feature at the SQL Server instance level.

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


Create a Contained Database in SQL Server 2012 Using SQL Server Management Studio

Once the Contained Database Feature is enabled at the SQL Server instance level, then create a Contained Database.

1. In the Object Explorer, right click the Databases and select New Database... option from the drop down list.
2. In the General Page you can provide the name of the database as DemoContainedDB and then click on Options page on the left side pane.
3. In the Options Page, to create a Contained Database you need to choose the value as Partial in the Other Options drop down list as highlighted in the screen shot below.

Create a Contained Database in SQL Server Denali Using SQL Server Management Studio

4. Finally, click OK to create the Contained Database.


Create a Contained Database in SQL Server 2012 Using T-SQL code

You can create a Contained Database by executing the T-SQL code below.

Use master
GO
CREATE DATABASE [DemoContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY 
( 
NAME = N'DemoContainedDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoContainedDB.mdf' , 
SIZE = 3072KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'DemoContainedDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoContainedDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO


Create a SQL Server User to Access the Contained Database

Once the contained database is created successfully, next create an SQL Server or Windows User, which will have access to the newly created contained database. Follow these steps to create a SQL Server User within the contained database.

1. In the Object Explorer, expand Databases, expand DemoContainedDB Database which was created earlier, expand Security and then right click Users and choose New User... option from the drop down list as shown in the screen shot below.

create a sql server user to access the database

2. In Database User - New window, you need to choose the User Type value as SQL User with Password and provide the SQL Server User Name and Password. You also have the option to select the Default language and Default schema. Finally, to create the user click the OK button.

used type value as sql user with password

3. You can also create the user by executing the T-SQL code below.

USE [DemoContainedDB]
GO
CREATE USER [ContainedDBUser] WITH PASSWORD=N'[email protected]', 
 DEFAULT_LANGUAGE=[English], 
 DEFAULT_SCHEMA=[dbo]
GO


Login and Verify the User Permissions on a Contained Database

Let's try connecting to the newly created Contained Database using the SQL Server Login.

1. In the Login tab you need to provide the Server name, choose the Authentication type as "SQL Server Authentication" and specify the Login and Password.

sql server authentication

2. In Connection Properties tab, you need to specify the database name as highlighted in the screen shot below in the Connect to database option and then click the Connect button to get connected to the Contained Database.

specify the database name

3. In the screen shot below, you can see that you were able to successfully get connected to the Contained Database using the newly created SQL Server Login which doesn't have access to the Database Engine.

using the newly created sql server login

4. You can also see in the screen shot above that the SQL Server Login (Contained User) only has access to the newly Contained Database.

5. Now let us try connecting to the SQL Server Instance using System Administrator (SA) privileges. Here you will be able to see all the databases. However, you will not be able to see the Contained Database User under Server Logins as shown in the screen shot below.

connecting the sql server instance using sa privilages
Next Steps


Last Update:


next webcast button


next tip button



About the author





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.



    



Friday, July 13, 2018 - 2:02:47 AM - Parag Back To Top

Hello Ashish,

Issue resolved.

I was trying to browse database and I was not allowing me to as it is contained user while connecting.

When I typed in database name with username and password it worked.

My Bad..

 


Thursday, July 12, 2018 - 3:43:31 PM - Parag Back To Top

Hello Ashis,

I have created contained database and user as per instruction but I am not able to log in using sql server credentials for user.

[Your method works fine for my local instance but instances installed in VM are giving me trouble.]

Below is error message.
======================

TITLE: Connect to Server

------------------------------

 

Cannot connect to 10.10.1.10,1433.

 

------------------------------

ADDITIONAL INFORMATION:

 

Login failed for user 'containeduser1'. (Microsoft SQL Server, Error: 18456)

 

Login failed for user 'containeduser1'. (.Net SqlClient Data Provider)

======================

 

I am creating this database on VM having windows 2012.

SQL instance running on port 1433 static [10.10.1.10 server name].

Firewall is enabled.[this can be issue please let me know if this db runs on new port or something.]

Also I have created user with same credentials in active directory to see if that can resolve issue but no luck.

Can you please help?

Regards,

Parag K.


Tuesday, December 16, 2014 - 9:22:50 AM - Thomas Franz Back To Top

You should ensure, that you have no orphaned procedures / functions / views (that links to objects which no longer exists) in your database (maybe http://www.mssqltips.com/sqlservertip/3240/finding-sql-server-objects-that-reference-invalid-objects/ will help by finding them).

Otherwise the ALTER DATABASE statement will fail with a very general 5069 error (technical it is a crash in a called DLL file). In the windows event viewer you would get an entry that refers to the SQLDump00xx.log file in the SQL Server log directory where you will get an list of the orphaned objects too.


Monday, August 11, 2014 - 2:39:36 PM - Junior DevDBA Back To Top

Just an FYI - it is advisable to review the pros and cons of contained databases, as described at the link below. Perhaps a pro/con list can be added to this MSSQLTips article.

http://www.sqlcoffee.com/SQLServer2012_0009.htm


Monday, August 04, 2014 - 3:37:48 AM - pradeep Back To Top

Any other use of contained DB.


Tuesday, April 22, 2014 - 12:47:49 AM - Venkataraman Ramasubramanian Back To Top

Thanks for this tip. Very useful.


Learn more about SQL Server tools