Enable SQL Server Contained Database Authentication

To create a contained database on SQL Server, using sp_configure 'contained database authentication' configuration must be set to 1. Otherwise, SQL Server administrator or database developers will face the following error message:

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. (Microsoft SQL Server, Error: 12824)

In this SQL Server tutorial, I tried to share SQL sp_configure script to enable 'contained database authentication' which will enable database professionals to create contained database on SQL Server.

While using SQL Server Management Studio to create new database on my SQL Server 2014 instance, I set the Containment type property of the new database to Partial. My aim to change the containment type is to change the database properties like Default Fulltext Language LCID and Default Language.

Unfortunately SQL Server database engine displayed the following error message, when I pressed OK button to create new database on SQL Server instance.

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. (Microsoft SQL Server, Error: 12824)

As you see the run_value of the SQL Server configuration parameter contained database authentication is 0.
SQL Server database administrators or developers who have the required permission can execute following sp_configure command to set contained database authentication to "1".

That is all! Right after altering the SQL Server configuration option from 0 to 1, I could successfully create a contained database on SQL Server 2014 instance.