Step-by-Step guide to Implement Contained Databases - SQL Server Denali

Who is online?  109 guests and 0 members
home  »  articles  »  Step-by-Step guide to Implement Contained Databases - SQL Server Denali

Step-by-Step guide to Implement Contained Databases - SQL Server Denali

change text size: A A A
Published: 12/23/2011 2:58:35 AM by  Sarabpreet Anand  - Views:  [7436]

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 Smile

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. http://www.sqlservergeeks.com/blogs/sarab/sql-server-bi/400/what-all-changes-require-a-restart-of-sql-service

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

 

 

Script:

sp_configure 'contained database authentication',1
reconfigure

 

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: 

CREATE DATABASE sarab_TEST_CDB
containment = partial 
go

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

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

 

Fig2. Options for User Type

 

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

CREATE USER Tim WITH PASSWORD = 'strong_password'
Go


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

 

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.

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?”

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

 

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

 

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

 

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


If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

Regards
Sarabpreet Singh
@Sarab_SQLGeek
@SQLServerGeeks

tags : sql 2012, Connect to SQL Database without Login, Contained Database, Containment, Create Contained database, Partial CDB, User with Password, User without Login, SQL Server Denali
  To rate this article please  register  or  login

Author

Sarabpreet Anand Sarabpreet Anand (Member since: 3/15/2011 5:38:06 AM)
SQLServer-MVP, Vice President - SQLServerGeeks.com

Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...

Follow Sarab on  Twitter@Sarab_SQLGeek ,  Facebook ,  LinkedIn

Comments (5)

simranjindal
1/9/2012 10:12:10 AM Simran Jindal said:

Thank you for the post. You saved me some research time. I have linked your post to one of my earlier posts on my blog at http://simranjindal.wordpress.com/. Hope that it is alright with you. Cheers

by
Deepraj
3/29/2012 8:41:34 AM Deepraj said:

Hi,

Really a very good post.

I have followed exact steps but while connecting it is giving 18456 error(Login failed).

Kindly suggest.

 

Deepraj

by
Deepraj
4/2/2012 8:11:49 AM Deepraj said:

Ok got it.

I have to type the name of database to whom i need to connect.

Thanks for the post again.

Deepraj

by
pitambar
10/9/2012 6:51:19 AM pitambar das said:

Thankyou sir for documenting this step by step, i tested and learned this within 20 minutes.

once again thankyuo

by
sarab
10/9/2012 8:37:41 AM Sarabpreet Anand said:

Thanks Pitambar,

Glad you liked it. 

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Articles RSS Feed

Most Recent Articles