Contained Databases – Server Setting Matters

In doing some additional testing on contained databases, I decided to create a new database on a new test VM.

 containment = PARTIAL

To my surprise, I got this error:

Msg 12824, Level 16, State 1, Line 1

The sp_configure value ‘contained database authentication’ must be set to 1 in order to create

a contained database.  You may need to use RECONFIGURE to set the value_in_use.

I checked the server setting, and sure enough the instance property was set to 0 (false).

At first you might think this shouldn’t matter, but imagine you go to attach a backup of a contained database to an instance that doesn’t have this enabled. However there are a few security and administrative concerns over contained databases. We have the password policies, the potential collision of user names, and more.

The easy fix is to enable the instance level setting. That’s easily done with this code:

-- Set advanced options
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'contained database authentication', 1;
EXEC sp_configure 'show advanced options', 0;

What about restoring a contained database backup? Surely it will just come online without the contained authentication?

I tried it, before running the script above, and I got this error:


Clearly the instance level setting matters. It’s easy to change, either in script or the GUI. However if you use the GUI, please don’t click OK and save the changes. Use the script button, save that for your logging/documentation, and then run the script.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to Contained Databases – Server Setting Matters

  1. Richard says:

    “Clearly the instant level setting matters.”
    Or the *instance* level setting, perhaps? 🙂

Comments are closed.