SQL Server 2012 introduces the partially-contained databases where the dependency on SQL Server instance is significantly reduced. One of the most important dependencies that can be bypassed in partially-contained SQL Server 2012 databases is the databases dependency on the SQL Server logins mapped to database users. You can create a database user with password in a partially-contained database and then connect directly to that database. No SQL Server login principal is needed. You will need to specify the initial catalog (name of the database) you are connecting to. SQL Server will verify that this database exists, and then will let the database handle the authentication.
To be able to use partially contained databases, you will need to enable them at the instance level. In Object Explorer, right-click the server name, and then click Properties. In the Server Properties go to Advanced settings and set Enable Contained Databases to True.
Then you will need to set the Containment Type to Partial in the Database Properties of your database.
You can also set the containment type option to partial during the creation of your database. The system databases such as model database cannot be converted to partially contained databases.
The next step is to create user(s) with password(s) inside your partially contained database(s). In the Object Explorer of your Management Studio go to Databases >> YourDatabase >> Security, right-click on Users folder and select Properties. In the Database User properties go to General and select SQL user with password in the User type drop-down menu. Then enter User name and Password for your new user.
On the Membership page of your Database User option select the fixed database roles that you want your new contained database use to be part of.
Once the contained database user is created, you can connect to that database directly. To do so, you will need to specify the Initial Catalog, i.e. the database to which you are connecting with this user. When connecting to a contained database with Management Studio, in the Connect to Server dialog box, enter contained database user and password, and hit Options button.
In the Connect to database box type in the name of your contained database which you are connecting to.
If no initial catalog is specified, connection to a contained database will not be made, but the authentications will be passed to SQL Server instance instead. If the SQL Server login with the same name exists, connection will be made to the default database of that login. Contained database can have both SQL user with password and SQL user with login, therefore you can establish the connections to your database either directly with contained database user with password or through SQL Server login principal. Below is the approximate algorithm of the authentication process.
When you connect to a contained database with contained db user, you will see this database only. No SQL Server instace objects or other user databases will be displayed. Contained database user can still access mater and tempdb system databases as a guest, as well as other user databases on the same instance where the guest user was enabled.
Contained databases do not support change data capture, change tracking and replication,
Temporary tables in contained database are collated with the collation of that database instead of the collation of tempdb as in non-contained databases.
Contained databases are important in high availability solutions like AlwaysOn Availability Group. When databases are failed over another instance, it is easier for applications and users to re-connect to those databases as their connections do not depend on the SQL Server logins in a new instance.