Contained databases allow authentication to be performed at databases level whereas, for non-contained databases, the authentication must be performed at SQL Server instance level. In this article, we will learn how to switch non-contained user databases to Contained databases.
Database with metadata
For Contained databases, the metadata is maintained within the database. This makes the contained database to appear virtually separated from all other SQL Server databases that are hosted on a SQL Server instance. A single collation is used to define all metadata of a contained database. This allows user authentication to be performed at the database level and reduces the dependency of the database on the SQL Server logins. As metadata is saved within the database, database migration can be easily performed without worrying about the migration of SQL Server logins.
Setting the instance’s property
Connect to your SQL Server through SSMS. Right-click on the server name and click “Properties” to bring the properties window. On the page “Advanced”, the field “Enable Contained Databases” will have two options in the drop down. Select “True” and press Ok to save and close the properties window. You can also do this via TSQL. After connecting to your SQL Server instance through SSMS, open a new query window and execute the below script.
sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'contained database authentication', 1 GO RECONFIGURE WITH OVERRIDE GO
After executing the script, the messages window should look like this
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option 'contained database authentication' changed from 1 to 1. Run the RECONFIGURE statement to install.
After connecting to your SQL Server instance through SQL Server management studio, from the object explorer, expand the folder “Databases” and then select your target database. Right-click on the database and click Properties. On the page “Options”, the field “Containment Type” will have two options in the drop down. First is the None and the second is Partial. Select Partial and then press Ok to save the configuration. Now the selected database is a contained database.
Update the placeholder <Your database name here> with the actual database name and then execute this script from a new query window to convert your database into a contained database
USE [master] GO ALTER DATABASE [<Your database name here>] SET CONTAINMENT = PARTIAL WITH NO_WAIT GO
View all contained databases
To view all contained databases in your SQL Server environment, add all your SQL Servers as Registered servers using SSMS and execute the below script from a new query window.
SELECT name as 'Database Name', case containment when 1 then 'YES' else 'NO' end as 'Is Contained?' FROM master.sys.databases WHERE containment <> 0; GO
Though contained database is different from non-contained user databases, you can continue to backup contained databases using SQL Server maintenance plan and this will help you in data disaster. Also a powerful SQL Server repair tool should also be included in the plan.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt pst and excel recovery software products. For more information visit www.datanumen.com