Contained Database and Windows User Group May 20th, 2013

Vinod Kumar

We discussed at length about what contained databases are, how to configure, steps to achieve the same etc in our previous post (Contained Databases inside SQL Server 2012). In this post let me show two concepts:

  1. Contained Database with Windows Users – most of the times I have seen people show contained database with SQL Authentication. In reality it applies to Windows Users too.
  2. Set a default Schema for group – This is an enhancement inside SQL Server 2012 where we can set an default schema for Windows User groups too.

Create Windows Group

Let us first create the Windows Group and add two users into this group. In our example, I am adding Balmukund and Pinal into this group. This will be done under Computer Management –> Local Users and Groups –> Groups location.

image

Create Contained Database

The steps are same as described in blog post Contained Databases inside SQL Server 2012.

SP_CONFIGURE ‘show advanced options’, 1

GO

RECONFIGURE

GO

SP_CONFIGURE ‘CONTAINED DATABASE AUTHENTICATION’, 1

GO

RECONFIGURE

GO

 

CREATE DATABASE cdb1 CONTAINMENT=Partial

GO

The above statements create the database CDB1.

Create Users and Set Default Schema

First step is to create the users and then give permissions to access our database. Change the same appropriately to suit your needs.

USE CDB1

 

— Create a database user for Windows Group

CREATE USER [MTC-Vinod\CDBUsers]

 

— Giving Permission to group

ALTER ROLE DB_DATAREADER ADD MEMBER [MTC-Vinod\CDBUsers];

ALTER ROLE DB_DATAWRITER ADD MEMBER [MTC-Vinod\CDBUsers];

Next step will be to create the schema and give permission to the group:

CREATE SCHEMA Users AUTHORIZATION [MTC-Vinod\CDBUsers];

 

— Set Default schema for a group!

ALTER USER [MTC-Vinod\CDBUsers]

WITH DEFAULT_SCHEMA = Users

Let us create a test table with some value to do a quick testing.

CREATE TABLE Users.testing(c1 int)

GO

INSERT INTO Users.testing VALUES (10)

Testing Contained Database

In our example, I have used a local user account for the demo. We can start SSMS using the “runas” command which will initialize SSMS in Balmukund’s user credentials. The typical command will look as:

image

In SSMS menu, select Advanced properties and don’t forget to change your default database to CDB1, else you are likely to get an error. Now the Object explorer shows as:

image

When you are connecting with Container Database user credentials, we can see ONLY this specific database. All other databases are hidden from the user.

Next step will be to query our tables. In the example below you can see we resolved the table “testing” to our default schema of “Users”.

image

Final Words

The above code confirms the two concepts we wanted to learn – a) Using Windows users and groups for Contained databases and b) Setting default schema for Windows Groups. The second concept is not specific to Contained databases but can be used for normal DB too. 

How many of you use contained database today? Are these cool additions with SQL 2012?Do let me know and would love to hear your feedbacks.

Tags: , , , , , , ,

This entry was posted on Monday, May 20th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


2 Responses to “Contained Database and Windows User Group”

  1. siva says:

    Hi Vinod,

    Thank you very much for your valuable tip.

    Its really helpful for us in 2012.

    Q:- Right now we are using sql server 2008 R2, When every failover occure for login sync using sp_revlogins we have copied all the logins from pricipal server to mirror server, in case we miss this step it will escalting i did this mistake few times.

    So as per your suggestion in 2012 using Database partial option means all the logins automatically it will move to Principal server to Mirror server my understanding is wrong please correct me.

    Best Regards
    Siva

Leave a Reply