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:
- 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.
- 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.
Create Contained Database
The steps are same as described in blog post Contained Databases inside SQL Server 2012.
SP_CONFIGURE ‘show advanced options’, 1
SP_CONFIGURE ‘CONTAINED DATABASE AUTHENTICATION’, 1
CREATE DATABASE cdb1 CONTAINMENT=Partial
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.
– 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)
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:
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:
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”.
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.