This content is part of the Essential Guide: SQL Server 2012 features: Weighing the benefits, limitations
Problem solve Get help with specific problems with your technologies, process and projects.

The ins and outs of the SQL Server 2012 contained database

Contained databases could be one of the most important features of SQL Server 2012.

Of the many features introduced in SQL Server 2012, the SQL Server 2012 contained database could prove one of the most valuable. Unlike the typical SQL Server database, a SQL Server 2012 contained database is one that's isolated from the SQL Server instance on which it resides, as well as from other databases on that instance. Such isolation makes managing databases easier, particularly when they're being moved to a different instance or implemented in a failover cluster.

Prior to SQL Server 2012, all SQL Server databases were considered non-contained. Metadata specific to the database resided outside that database. The server's default collation could impact queries against the database. And user authentication within the database was tied to the server-level logins defined on the instance.

In SQL Server 2012, all databases are still, by default, non-contained. However, you can now configure any non-system database as contained. That way, metadata will reside within the database it describes. In addition, because the collation model has been greatly simplified, all user data and temporary data will use the default database collation, and all other objects (metadata, temporary metadata, variables, among others) will use the catalog collation, which is Latin1_General_100_CI_AS_WS_KS_SC for all contained databases.

The most significant change that the SQL Server 2012 contained database brings is the "contained user," a user account created specifically for the contained database. The account is not tied to a server-level login and provides access to the contained database only, without granting permission to other databases or to the instance as a whole.

SQL Server supports two types of contained users:

  • Database user with password: A local database account created with a username and password that are authenticated by the database.
  • Windows principal: A local database account based on a Windows user or group account but authenticated by the database.

You can add either one or both account types to a contained database. In fact, you can still add login-based accounts as well. That's because SQL Server 2012 supports what are referred to as "partially contained databases," rather than fully contained ones.

In a fully SQL Server 2012 contained database, no dependencies, such as a Service Broker route or login-based user account, can exist outside the database. But a partially contained database can support both contained and non-contained elements. That means, for example, that you can provide access to the database either through login-based accounts, contained user accounts or both. However, you can still achieve the equivalent of a fully contained database by eliminating any non-contained elements. (Whether SQL Server will eventually support fully contained databases is yet to be seen.)

The SQL Server 2012 contained database

After you've isolated your SQL Server 2012 contained database, you can easily move it from one SQL Server instance to another, without having to move a set of SQL Server logins. The contained database stores all the information it needs within that database. This process also makes it easier to set up your high-availability clusters. Because users connect directly to the database, they can easily connect to a second database if failover occurs.

Even if you're not moving or clustering your databases, the SQL Server 2012 contained database can make user account management easier because you're not trying to administer both SQL Server logins and database user accounts. You grant access to specific users to specific databases, without those users being able to access anything outside them.

Yet all this good news doesn't come without a few downsides. For example, a contained database cannot use replication, change tracking or change data capture. And a contained database can raise security concerns. For instance, users granted the ALTER ANY USER permission can create user accounts and grant access to the database. In addition, the password hashes associated with contained user accounts are stored within the database, making them more susceptible to dictionary attacks. Plus, the contained user accounts cannot use Kerberos authentication, which is available only to the SQL Server login accounts that use Windows Authentication.

Despite their limitations, if contained databases are carefully implemented, you can sidestep some of the security issues and reap the benefits that database isolation provides. The SQL Server contained database offers a level of portability and manageability not seen before in SQL Server. Moving databases is easier. Failover is easier. Managing access is easier. Indeed, the contained database feature in SQL Server 2012 could prove beneficial for any organization looking to streamline its operations.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at his website.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.