In SQL Server 2012, they have introduce the Contained Database. What is the real purpose of this feature? What drawbacks of previous versions has it fixed?
What is the real benefit of contained databases
They are being developed to make migration of databases between systems easier (both your databases, and databases on SQL Azure that they need to move around to balance resources). Anything that has a dependency outside of the database is considered a risk, because it's extra scaffolding that has to go with the database - easy to forget, easy to get wrong, easy to fall out of sync.
For example, in Denali these issues are addressed:
Today when you move a database to another server, you also have to migrate all the SQL logins at the server level - this can be a pain especially when the SIDs get out of sync. With contained databases, database-level users that don't have a tie to a SQL Server login just come along for the ride when a database is backed up, detached, mirrored, replicated, etc. Nice and easy.
If you have a database with collation that differs from the server collation, you may find that you have collation conflicts when you join or perform other operations with #temp tables, because the #temp tables that get created will inherit the server collation, not the calling database. While you can get around that by specifying a COLLATE clause on every single column reference, with contained databases, #tempdb inherits the collation of the calling database, overriding the server collation.
THROW() can almost fall into this category as well - since you no longer have to use sys.messages to store custom messages. This is not as common as the above two issues, but it certainly does make migrating to a new server work better if there is no requirement to also keep sys.messages in sync. This is not restricted to contained databases, but it plays the same role.
For things that don't meet "containment" criteria, there is a DMV that can show you a list of things that will potentially break if you move them to another server. For example, a call to a three- or four-part name.
In future versions, there are other issues that will be addressed. For example:
SQL Server Agent is an external dependency. When you move a database to a different server, SQL Agent jobs that reference that database do not automatically move with the database, you have to determine which ones are affected and script them out yourself (it is not quite as simple as just bringing along msdb too). In a future version of SQL Server, I envision that either (a) each database will be able to have its own Agent, or (b) Agent will be moved to an OS-level architecture, where some translation layer tells you where the database is, instead of having to have Agent live on the same machine. The latter option can get complicated when we're talking about Azure, geo-disparate networks, etc.
Linked Servers are also an external dependency. This could be easily solved with database-level linked servers - especially since these are little more than synonym containers / pointers.
There are others, but those are the heavy hitters.