One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

Enable the Instance setting

There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

Once you click the script button, you’ll get this T-SQL:

EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

This will enable containment for the instance and you’ll be halfway there.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in  2012. Again, please don’t click OK, but click script.

The code will appear as below:

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

You can also set this value when you create a database:

-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL

That’s it.

These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.

Filed under: Blog Tagged: ContainedDatabases, sql server, syndicated