Last Sunday I spent some time exploring the features of SQL Server Denali. One of the new features is Contained Databases. In short a Contained Database is independent of the SQL Server Instance. All the database metadata and user information is stored inside the database itself. A user defined in this database need not have a corresponding login. This feature is very useful in Non-Production environment where the databases are moved across instances. No more orphaned users or syncing of logins across instances. This MSDN article has more details on Contained Databases.
Configuring Contained Databases Property
Contained Databases feature is not enabled by default. It can be enabled through T-SQL as below.
sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'contained database authentication', 1 GO RECONFIGURE WITH OVERRIDE GO
It can also be enabled by setting the Enable Contained Databases to TRUE using SSMS GUI as in this screenshot.
Creating Contained Databases
After enabling this option, the next step is to create a Contained Database. While creating the database, changing the Containment type to Partial will make the database as Contained. By default this option is set to None.
It can also be created using the below T-SQL command.
CREATE DATABASE [ContainedExample] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'ContainedExample', FILENAME = N'C:ContainedExample.mdf' ) LOG ON ( NAME = N'ContainedExample_log', FILENAME = N'C:ContainedExample_log.ldf')
Creating Users with Password
In this database like in earlier versions of SQL Server, users can be created which are associated with logins. The new feature is that a new user with password which is not associated with a login can also be created for Contained Databases. Like I mentioned earlier, the password is also stored within the database.
The T-SQL command for creating a user with password is as below.
USE [ContainedExample] GO CREATE USER [username] WITH PASSWORD=N'Strong@Password' GO
Connecting to SSMS
In SSMS just entering the user name and password of this user is not enough to connect to the instance. Since this user does not have an associated login, SSMS raises the following error.
Error: 18456, Severity: 14, State: 5.
Login failed for user 'containeduser'. Reason: Could not find a login matching the name provided.
In order to connect using the credentials of a user with password through SSMS, the Contained Database name needs to be entered in the Connect to Database drop down box.
“Un-Containing” the database
A Contained Database can be “un-contained” as a normal database. The database property can be changed in SSMS or using a T-SQL command as the one below.
USE [master] GO ALTER DATABASE [ContainedExample] SET CONTAINMENT = NONE GO
If the Contained Database has an user with password, this command would fail with the below error.
Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Before changing the Containment option, SQL Server tries to create login within the database (most probably creating a user for the login executing the command. I was not able to track this down), hence this command would fail. After dropping the user with password I was able to change the Containment option to None. If the database does not have an user with password, the Containment option changes without any errors.
Converting a database to a Contained Database
A database can be converted to a Contained Database using both SSMS and T-SQL.
USE [master] GO ALTER DATABASE [databasename] SET CONTAINMENT = PARTIAL GO
If this database has users associated with Windows Logins they are contained in the database without any extra effort. In order to contain the users which are associated with SQL Server logins, sp_migrate_user_to_contained stored procedure needs to be executed. This will convert the SQL Server Logins to users with password. The following command will convert a user associated with a SQL Server Login named tlogin to a user with password.
USE ContainedDatabase GO sp_migrate_user_to_contained @username = N'TLOGIN', @rename = N'keep_name', @disablelogin = N'do_not_disable_login' ;
Once this is done, the newly contained database is ready to go.
Contained Databases is a very useful feature. This reduces a lot of work for the DBA while shipping the databases from one environment to another. But the Security Auditors will need to update their auditing procedures due to the threats mentioned here.