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




Related Articles :

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 |