One of the reasons for contained databases (see my previous post) is to allow for a more seamless transition when moving a database from one server to another. One of the biggest complications in doing so is making sure that all of the logins are in place on the new server. Contained databases help solve this issue by creating a new type of user: a database-level user with a password. I want to stress that this is not the same concept as a user without a login, which serves a completely different purpose altogether. To avoid confusion, I'm going to call these database-level users "contained users."

To create a contained user, you must be in the context of a contained database. So let's create a couple of contained databases:

CREATE DATABASE CDB1;
GO
CREATE DATABASE CDB2;
GO
 
ALTER DATABASE CDB1 SET CONTAINMENT = PARTIAL;
GO
ALTER DATABASE CDB2 SET CONTAINMENT = PARTIAL;
GO

With those databases in place, we can create a contained user named Bob in each of these databases. We'll make the passwords the same just to keep things simple.

USE CDB1;
GO
CREATE USER Bob WITH PASSWORD = N'bar';
GO
 
USE CDB2;
GO
CREATE USER Bob WITH PASSWORD = N'bar';
GO

When you try to connect to this server using Management Studio, you might be tempted to just type the user name and password and click Connect. This won't work, because the user Bob is not a SQL login and does not have a default database, so SQL Authentication fails:

Since no database is provided, SQL Server isn't able to check if there is a contained user – the engine is not going to go through the hassle of checking every single database and letting you into the first one that works. However, if you click the Options >> button, on the Connection Properties tab there is a "Connect to database:" option. But when you try to specify the database, the drop-down only offers <default> (which we've already established won't work), and <Browse server...>:

If you choose the <Browse server...> option, it brings up this prompt:

If you say Yes, the connection immediately fails with the same "Login failed" error as before, because the contained user does not have access to the list of databases on the server.

This is by design. And there is a fix for all of this, if you go back to the Connection Properties tab. While the "Connect to database:" option appears to be a dropdown, it is actually a combo box, so you can type the name of your database, and this will allow you to connect. You should create a registered server with these options so that you only have to perform this action once.

Once you're connected, you'll notice some slightly different behavior in Management Studio. The Object Explorer list is quite sparse – as a contained database user, you don't have access to any of the other databases on the system (except for master and tempdb), or to server-level entities such as SQL Server Agent and the Security and Management nodes:

You'll also notice in a query window that the Use database dropdown list contains only the three databases you see above: the contained database you connected to, as well as master and tempdb. You don't get visibility into the other databases you are also "contained" within, even though the username and password match (the sids do not, if you care to inspect the sys.database_principals view in each database). In order to connect to that database, you need to change your current connection to target that database instead (you can do this by right-clicking within the query window and selecting Connection > Change Connection, and following the steps as above).

For fun, I tried to see what would happen if I tried to USE the other database:

USE CDB2;

This yielded:

The same error message occurs if I try to perform a join between the two databases, or try to access a synonym that underneath accesses a 3-part name referencing the other database. This is more or less what I expected, because the user is truly contained to this database, even though there happens to be a user with the same name and password in another database. However, I hope this functionality is enhanced in the future… I believe that it would fit nicely with availability groups if I can fail over multiple databases as a unit, and also bring over contained users that can access any of the databases within the availability group. This seems to be a more real-world scenario than a user that can only interact with and within a single database.