As you already know from Chapter “Data Definition Language“, contained databases have no configuration dependencies on the server instance where they are created and can therefore be easily moved from one instance of the Database Engine to another one. In this section you will learn how to authenticate users for contained databases. Each user that belongs to a contained database is not tied to a login, because such a user has no external dependencies and can be attached elsewhere.
Example 12.21 shows the creation of such a user.
Example 12.21 USE my_sample; CREATE USER my_login WITH PASSWORD = 'x1y2z3w4?';
Example 12.21 creates a user my_login that is not tied to a login. (The my_sample database is a contained database that was created in Example 5.20.) If you try to create such a user in a convenient database, you get the following error:
Msg 33233, Level 16, State 1, Line 1 You can only create a user with a password in a contained database.
The system stored procedure sp_migrate_user_to_contained converts a database user that is mapped to a SQL Server login to a contained database user with a password. sp_migrate_user_to_contained separates the user from the original SQL Server login, so that settings such as password and default language can be administered separately for the contained database. This system stored procedure removes dependencies on the instance of the Database Engine and can be used before moving the contained database to a different server instance.
Example 12.22 shows the use of this system stored procedure.
USE my_sample; EXEC sp_migrate_user_to_contained @username = 'mary_a', @rename = N'keep_name', @disablelogin = N'do_not_disable_login' ;
Example 12.22 migrates a SQL Server login named mary_a to a contained database user with a password. The example does not change the username and retains the login as enabled.
Also, you can use the dynamic management view called sys.dm_db_uncontained_entities to learn which parts of your database cannot be moved to a different server instance.
