Script to convert a database to contained database in SQL Server

What is Contained Database in SQL Server 2012?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.
Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.


To Set-up  contained databases  is a simple process which involves the following being carried out within SSMS:-

sp_configure 'contained database authentication'
 ,1
GO

RECONFIGURE

We can convert a non contained database to a contained database simply by setting its CONTAINMENT property,

USE [master]
GO

ALTER DATABASE [marketing]

SET CONTAINMENT = PARTIAL;

The users mapped to SQL logins can be converted to contained database users, using the sp_migrate_user_to_contained system
procedure 

SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.NAME + ''',
@rename = N''keep_name'',
@disablelogin = N''do_not_disable_login'' ;'
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
 AND sp.is_disabled = 0;

This code returns execute statements copy it and execute

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.


If you move a non-contained database from one server to another, by means of backup/
restore or detach/attach, then there is a chance that your SQL users will become
orphaned, meaning that they will have no corresponding login. As the mapping between logins
and users is done by the SID, if a login is present on the destination instance with the same
name but another SID, then the user will not recognize it and will be orphaned.
If you are moving the database to another server in the same domain, the
user to login mapping problem occurs only with SQL logins, because the SID
used for Windows logins is the same as the domain SID set in Active Directory.
Thus it is the same on every instance where this login is created.