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

 

SQL Server 2012 Contained Database Feature


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

SQL Server Indexing Tips and Tricks - Our next free MSSQLTips Webcast


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:


signup 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    Notify for updates 


Send me SQL tips:

    



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