SQL SERVER – ‘Denali’ – A Simple Example of Contained Databases

Recently I was asked with the question: What is new for Database Security in SQL Server “Denali”?

I think this is a very interesting question as I always wanted to talk about Contained Database, and this question gives me the chance to do so. Let us start with discussing contained database.

A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

The real question is, “What about users who are connecting to this database?” Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Let us try out this feature on SQL Server Denali. We will do the following steps:

  1. Enable Contained Database
  2. Create Contained Database
  3. Create User in Contained Database
  4. Try if the user can access outside Contained Database

We can do various tests on this subject; however, in this blog post we will limit out exercise to the above four points.

Enable Contained Database

Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Create Contained Database

CREATE DATABASE [ContainedDatabase]
CONTAINMENT
= PARTIAL
ON PRIMARY
( NAME = N'ContainedDatabase', FILENAME = N'C:\ContainedDatabase.mdf')
LOG ON
( NAME = N'ContainedDatabase_log', FILENAME = N'C:\ContainedDatabase_log.ldf')
GO

Create User in Contained Database

USE [ContainedDatabase]
GO
CREATE USER ContainedUser
WITH PASSWORD = 'pass@word';
GO

Try if this user can access out side Contained Database

To test this, we will attempt to login in the database with default settings (where login database is the master).

When we attempt this, we will be not able to login in the server simply because the user does not exist at the server level.

Now, let us try to login in the system using the username which was created in the Contained Database.

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

We will tackle more about this interesting subject in the future.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – ‘Denali’ – A Simple Example of Contained Databases

  1. Interesting concept. Kind of curious as to where this would be applicable. Let us say we create a contained db which is case insensitive, then move it to a case sensitive server, the contained db would still be case insensitive?

    Thank you

    Like

    • Ramdas, Database Collation and Instance Collation can be different. We can create a Database using collation “X” (say) on an Instance with collation “Y”. So, databases can be moved freely from one Instance to another, and the database collation will still remain unchanged. This is true for all databases, and not just contained databases.

      Like

      • One advantage that you get, though, is that temporary objects created in the TempDB will use the collation of the “Contained Database”, instead of the collation of the server. Temporary objects created from multiple databases, will, however, continue to create issues due to Collation Conflicts.

        Like

  2. Contained databases is a perfect situation if we need to migrate database from one server to another as there is no dependency.

    In real world situation,we actually cannot determine during the design part of a database that it needs to be migrated or can be a candidate for migration to a different server,so I was wondering the implication of Microsoft Idea to initiate the concept of contained database.

    Could you please explain the need of contained databases in real world situation.

    -Anup

    Like

  3. I just wanted to say thank you for these terrific posts on Denali. I’ve been using SQL Server 2008 R2 Developer edition and I just installed the express version of Denali. I love it. Your posts opened my eyes to some of the newer features available and got me curious enough to try it. Keep up the great work.

    Like

  4. Intresting concept which kept in very simple manner thanks for this. Even I am much interested in knowing the real time scenarios where the contained databases used.

    Like

  5. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  6. Pingback: SQL SERVER – TechEd India 2012 – Content, Speakers and a Lots of Fun « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Restricting Access to Contained Databases using Logon Triggers | Journey to SQL Authority with Pinal Dave

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s