Getting started with Contained Databases in SQL 11 (“Denali”) – Part 02 – Creating contained databases & changes to ALTER DA

Yesterday, I attempted to explore the concept of contained databases. Today, we will see how to create our first contained database in the very latest SQL 11 (“Denali”) CTP01.

As discussed yesterday, contained databases are new to SQL 11, and hence if you need to experiment with contained databases, you will need a instance of SQL 11 (“Denali”) deployed and running.

To get us back into context, let’s revisit the definition of a contained database:

"A contained database has no external dependencies on server-based settings or metadata to define the database. Users defined in a contained database do not have a dependency on logins created on the server, and therefore contain all information necessary to authenticate a user within the database."

Creating a “contained” database

Here is a step-by-step guide to create your first “contained” database:

Assumption: It is assumed that the reader have fair amount of exposure to SSMS and SQL Server database fundamentals before beginning this exercise.

  1. Login to the SQL 11 (“Denali”) SQL Server Management Studio (SSMS) (while you are at it, enjoy the new Visual Studio 2010 shell)
  2. Right-click on the Server instance name in the Object Explorer and click “Properties”
  3. Select the “Advanced” page
  4. Set the “Enabled contained databases” to TRUE

    Alternate mechanism:

    --Enabled Advanced options
    sp_configure 'show advanced options',1
    --Enabled Database Containment
    sp_configure 'contained database authentication',1
    --Disable Advanced options
    sp_configure 'show advanced options',0
  5. Now, restart the SQL Server Instance by right-clicking the instance name in SSMS and selecting “Restart”
  6. After the restart, log back into SSMS, right-click on the Database node and select New database
  7. Name the database “ContainedDB
  8. Navigate out to the Options page and select the containment mode as “Partial”
    Alternate method (via T-SQL):

    --Notice that we now have a new option - CONTAINMENT!
    USE master;
    ( NAME = Sales_dat,
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAContainedDB.mdf',
        SIZE = 10,
        MAXSIZE = 50,
        FILEGROWTH = 5 )
    LOG ON
    ( NAME = Sales_log,
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAContainedDB.ldf',
        SIZE = 5MB,
        MAXSIZE = 25MB,
        FILEGROWTH = 5MB ) ;
  9. Click “OK” to create the database

Creating a contained user within the newly created contained database

  1. Login to SQL 11 (“Denali”) instance used in the test above via SSMS
  2. Using the Object Explorer, navigate out to “ContainedDB”->Security
  3. Right-click on “Users” and choose “New User”
  4. In the New User dialog, enter the user name and password of your choice to create a new user. I created a user called “ContainedUser”
  5. Navigate out to the Membership page, and select the “db_owner” checkbox

Let’s Login!

It’s time to login to SQL Server using our newly created user. But wait! Don’t we need that this database user is mapped to a login? No! That’s the whole beauty of contained databases!

  1. In the Object Explorer, click on the Connect drop-down list and choose “Database engine”
  2. Change the authentication to “SQL Server Authentication”
  3. In the login name and password boxes, pass in the login name and the password of the user created in the steps above
  4. Do not hit “Enter” key or click on “Connect”. Instead, click on the “Options” button on the “Connect to Server” dialog box
  5. In the Connect to Database box, type in the name of the contained database, in our case “ContainedDB”
  6. Now click on “Connect” to connect to the SQL Server
  7. Notice the level of rights that this user is granted
    NOTE: Although the user (ContainedUser) had db_owner membership, the server/instance-level objects are not exposed to this user under this connection.

Does this database contain any non-contained  entities?

So, now that the database and the users are created and login process tested, it is essential to ensure that the database is indeed contained and there are no uncontained entities.

Launch a new query editor window using the contained database connection created above, and execute the following query against the “ContainedDB”

SELECT * FROM sys.dm_db_uncontained_entities 

You should only see one record for the ROUTE class_desc. This is expected.


Changes to the ALTER DATABASE clause

With contained databases being a radical change in SQL 11 (“Denali”), there is no wonder that we have quite a few commonly used clauses which have changed. One of them is the ALTER DATABASE clause.

To study what changed, run the following query against the “ContainedDB”, and note the error that occurs.


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.

You did not expect that, did you?

Well, the behaviour of several commands has changed when used in context of a contained database. Please refer the Books On Line page ( for all the associated details.

Okay, now let us run the following:


The query will succeed, but for those who came in late, note the use of the CURRENT keyword, which indicates that all changes must be made to the current database only, and must not cross the application boundary.

Now run the following query:

ALTER DATABASE ContainedDB SET compatibility_level = 110; 

This query will be successful.

Finally, close the SQL Server Management Studio.

Some finer points…

  1. Only the partial containment mode is supported in SQL 11 (“Denali”) CTP01
  2. The following entities are not allowed in a partially contained database
    • Uncontained tables (for example, a table that uses an uncontained function in a constraint or computed column)
    • Check constraints that directly use uncontained entities or functions
    • Numbered procedures
    • Computed columns that directly use uncontained entities or functions
    • Schema-bound objects that depend on built-in functions with collation changes
    • Binding change resulting from collation changes, including references to objects, columns, symbols or types

Tomorrow, we will move this newly created database to another SQL Server 11 (“Denali”) instance, and see how easy it can be.

Until tomorrow,

Be courteous. Drive responsibly.


Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.