Contained Databases in SQL Server 2012

When you login as a contained user, make sure you enter
the correct database as the default database. In previous SQL Server versions, logins
are unique for the server instance. However, one contained user name can be in
another database. Therefore, it is essential to enter database name at the
login as shown below.

After login into the server, you will see following in
SQL Server Management Studio.

Which means you can only interact with the relevant database and its objects.

After creating contained specific objects like the contained
user that we created, you cannot be revert the database back to a ‘None’ contained database. Note the below error which will be encountered if you attempt to create a user using the standard method:

Msg 33233, Level 16, State 1, Line 1 
You can only create a user with a password in a contained database.

 Msg 5069, Level 16, State 1, Line 1 

ALTER DATABASE statement failed.

Apart from users, tables or temporary tables will be
created within the database. In previous editions, if your database collation
is different from server collation, there will be some issues with temporary tables.
The temporary table collation is a server collation therefore joining user
tables and temporary tables will result following error:

Cannot resolve the collation
conflict between "SQL_Latin1_General_CP1_CI_AS" and
"Danish_Norwegian_CI_AI" in the equal to operation.

With contained databases, all objects are in the database
itself, even temporary tables will be created inside the database. So you won’t
have such issues.  

There is a new DMV named sys.dm_db_uncontained_entities
which shows any uncontained objects in the database. If sys.dm_db_uncontained_entities
is empty, your database does not use any uncontained entities.

Migrating Users

In Contained Databases you also have the option of converting a non-partially
contained database to a partially contained database. However, after converting
the database, you need to manually convert users since existing database users
are not automatically migrated as well.

The following query will give you existing users in the database.

SELECT 
name, type_desc, authentication_type, authentication_type_desc

FROM sys.database_principals 

Note that, though the database is changed to
Containted, the users are still at INSTANCE level or server level. These users
can be converted by following query.

EXECUTE sp_migrate_user_to_contained

@username = N'Dev1',

@rename = N'keep_name',

@disablelogin = N'disable_login'

You can also rename the existing user as well as disable the
existing login.

After running the above query, if you execute the query you
executed before, you will see that the Dev1 user is now changed to the DATABASE level
which is s contained user.

Pages: 1 2




Array

One Response to “Contained Databases in SQL Server 2012”

  1. Nice introduction to contained databases.

    One thing I found is that the server authentication mode needs to be set to Mixed Mode (and not Windows Authentication). I guess this makes sense as the user accounts need to be independent of a domain.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |