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.
]]>