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).

SQL SERVER - 'Denali' - A Simple Example of Contained Databases contained1

SQL SERVER - 'Denali' - A Simple Example of Contained Databases contained2

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.

SQL SERVER - 'Denali' - A Simple Example of Contained Databases contained3

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

SQL SERVER - 'Denali' - A Simple Example of Contained Databases contained4

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.

SQL SERVER - 'Denali' - A Simple Example of Contained Databases contained5

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

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – TechEd 2011 – Random Question and Answers
Next Post
SQLAuthority News – Today is First April – April Fool’s Day

Related Posts

12 Comments. Leave new

  • aasim abdullah
    March 31, 2011 12:34 pm

    Interesting one

    Reply
  • 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

    Reply
    • 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.

      Reply
      • 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.

  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • I’d imagine this is leading up to SQL cloud databases. Very interesting, I’m sure we’ll find an application for this.

    Reply
  • Hi Pinal,

    Could you please let us know where exactly this contained database would be useful….?

    Reply
  • Hi Pinal,

    Could you please let us know where exactly this contained database would be useful ….?

    Reply
  • What about Agent Jobs that will also move while backup restore in Contined database

    Reply
  • Wilfred van Dijk
    November 22, 2014 4:57 pm

    Is this the solution for AlwaysOn and the missing logins on the secundary?

    Reply

Leave a Reply

Menu