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 (https://blog.sqlauthority.com)