It’s been a long time SQL Server 2012 got released and many organizations are already using 2012 for hosting their production databases. In this blog post let us see one of the striking feature of SQL 2012, “Contained Database” which microsoft advertised a lot during the initial phases of 2012/DENALI.
One of the major advantages of contained Database(s) is, getting rid of User-Login Mapping Issues/Orphaned Users Issues and all that mess. Think about copying a database/Refreshing Lower environments Scenario, where we have to deal with mapping users/fixing Orphaned Users blah blah…. With contained Databases, all we have to do is, copy the database and Voila done…. Basically, in a contained Database, the user credentails are created and stored within the database, hence no dependency on mapping SID’s and Server level Logins. let’s see it in action….
Before doing anything, the very first step you have to do is to enable “contained database authentication” at Instance level. You can do this using GUI(SSMS) or T-SQL as shown below.
Step 1: Enable contained database authentication at Instance Level
As you can see above, it is enabled on my Instance. If not, you can use sp_configure to turn it ON as shown below…
or using GUI(Go to SQL Instance Properties->Advanced)
Step 2: Create a contained Database
From GUI, Right click on databases and “NewDatabase…”and in options tab you have to make “Containment Type” as “Partial”, click Ok and boom….you have created a Contained Database 🙂 See below…I created a database called “Contained_Tst” for this demo purpose.
Step 3: Creating Users/Connecting to Contained Database
Once done creating Contained Database, you can create those special users(Without login dependencies) inside the DB. They can be either SQL Auth or Windows accounts. Navigate to Users under Security inside the database and select “new User…” as shown below.
Now, Key is to select the appropriate option(See below)
I’ve selected SQL User with password, created user called “Contained_User”
Now we are done with creating User! It’s that simple….Now let’s see how to connect to contained Database using that account.
Using SSMS:
If you connect normally, you will be getting the below shown error.
So, what are we missing here? The key is to select the actual contained database manually which the user has access to…Go to connection properties and type in the database name manually(Drop down will/might be empty) and click ok. It should work now without any issues.
Once connected, you can see only the contained_tst database in the object explorer as expected and suser_sname() giving some GUID instead of the User Name.(See below)
When you try to access a different database on the Instance, you will get the below security context error…
Connecting to contained Database from SQLCMD:
You will be getting the same error as we got in SSMS if you try to connect without providing the database name(See below)
As soon as I tried providing the Database with -d switch, it worked as expected(see below)
Can a contained Database have normal Users?
Yes, it can have a combination of both with out any issues. So, how do we identify which Database users are Database Scoped(Contained Users) and which users are Instance Scoped(Normal DB users as we used to have in the past)?
In SYS.DATABASE_PRINCIPALS, we have columns(authentication_type and authentication_type_desc) which shows the type of authentication for database user(s) as well….
For demo purposes, I’ve created 2 more normal users (tst1 and tst2) besides contained_user. Now let’s see the results of sys.database_principals, shown below
As you can see tst_1 and tst_2 are Instance scored Users.
Can we convert/Migrate Normal Users to Contained Users? The Answer is Yes, by using “sp_migrate_user_to_contained“!
In this example, let’s migrate Tst_1 which we created above as a normal user to contained user within contained_Tst database. See below screenshot
Voilaaa…..Done!
Caution: Make sure not to disable login while migrating unless you really want to. Because, this login might have a user mapping in some other databases which will break things severely.
Hope this gives a decent amount of exposure on contained databases and what can be achieved making use of them.
MSSQLTREK 