Hi Friends,

In my last blog “Contained Databases in SQL Server 2012 – Denali” I wrote about the basics of Contained /Partially Contained Databases and its advantages. In this article I’ll show you step by step, How to create partially contained Databases, its users (users with password & without Logins) and how they authenticate. So let’s begin

1.       How to Create Partially Contained Database.

Creating a contained DB is not at all a complex task; the only sequence you need to remember (which is quite logical also) is to Enable Contained DB Setting at Server Level before creating a Contained DB. This feature is disabled by default, why? The same old reason to minimize the surface area. (Security)

This feature is dynamic in nature and doesn’t require any restart. If you don’t know, How to find what all changes require SQL Service Restart? Read this blog

You can enable the setting either from SQL Server Management Studio or via T-SQL Command.

1_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

2_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Script:

Since the server is ready to support Contained Databases let’s create DB. Again you have two choices either to go for T-SQL or GUI.

For creating Contained DB viaT-SQL, use the new containment switch in Create Database command:

If you are GUI lover just open the Create Database window, choose the containment type to Partial in the Options Tab before hitting that Ok button and your Contained DB is ready.

4_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Now let’s check whether the created DB is actually a Contained DB or not, for this all we need to do is query sys.databases for containment and containment_desc columns.

2.       How to create Users without login (Users with password)

The first thing you’ll notice is the new and advanced window while creating the user for Contained Database using GUI. You get many options while creating a User, the first being User Type. This option depends what all other fields will be visible while creating the user.

Fig1. Shows the New User Creation Window

5_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Fig2. Options for User Type

6_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

You can also create user with password for your contained DBs using T-SQL, here is the script:

In Case you want to assign DB Role to these users, this can be achieved through Membership tab.

7_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Fig3. Membership Tab

3.       Connect DB without Login (using Users with Password) 

Open Connect to Server Dialog box, Enter Server Name, User Name in Login field and User Password in Password field and click Options.

8_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Now choose “Browse Server” option for Connect to Database Field, this will give you an Information Pop-up saying “Browsing available DBs require connecting to server which may take a few moments would you like to continue?”

9_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

10_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Clicking Yes will connect to Server and show you all the Databases in a separate window.

11_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Choose the Partial Contained Database you want to connect (your user should have connect access to the database) and press connect.

12_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

That’s it; you are now connected to a Database without a Login.

13_Step_by_Step_guide_to_Implement_Contained_Databases_SQL_Server_Denali

Hope you learned something new, do leave us a comment.

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

SQL Server Capture Deadlocks using Extended Events
SQL Server: SSRS – Moving Reports from Old to a New Environment