Four types of SQL Server Authentication

In SQL Server can be four types of user authentication.
To illustrate this I'll run following query in my test box:

SELECT authentication_type, name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE principal_id between 5 and 16383;

It gives me the following result:

So, there are four authentication types:
- 0 - NONE;
- 1 - INSTANCE;
- 2 - DATABASE;
- 3 - WINDOWS;

#1 - INSTANCE and #3 - WINDOWS are the most common authentication types: they are simply SQL and Windows authentications.

Will review the more exotic ones: #0 - NONE and #2 - DATABASE.

SQL Server User with "NONE" authentication:

"TestUser0" with NONE authentication is just a database user without SQL Server Login.
That means that user can't authenticate into a SQL Server. Because of that it can't be assigned to any person or an application using SQL Server.

That user account can be used within "EXECUTE AS" in functions, stored procedures and triggers.
That user account can own schemas and have privileges and permissions, which will allow restricted user to have an access to objects and actions via stored procedures or functions, which she/he is not granted to see or perform.

For example I can try to do a select as user "TestUser1" from newly created table.

USE TestDB;
GO
SELECT 1 as ID INTO tbl_TestTable;
GO
EXECUTE AS USER = 'TestUser1' ;
GO 
SELECT * FROM tbl_TestTable;
GO
REVERT ;
GO 

I'll get following error:

Msg 229, Level 14, State 5, Line 417
The SELECT permission was denied on the object 'tbl_TestTable', database 'TestDB', schema 'dbo'.

However, if I create a stored procedure with EXECUTE AS permissions for user "TestUser0" and grant access to that procedure to "TestUser1" then that user will be able successfully see content of a new table:

GRANT SELECT ON tbl_TestTable TO TestUser0;
GO
CREATE PROCEDURE usp_ViewTestTable
WITH EXECUTE AS 'TestUser0' AS
SELECT * FROM tbl_TestTable;
GO
GRANT EXECUTE ON usp_ViewTestTable TO TestUser1;
GO
EXECUTE AS USER = TestUser1';
GO 
EXEC usp_ViewTestTable;
GO
REVERT ;
GO 

Create user with NONE authentication is pretty simple, you just specify that you want to create a user with no SQL Server login:

USE TestDB;
GO
CREATE USER [TestUser0]  WITHOUT LOGIN;
GO

SQL Server User with "DATABASE" authentication:

That is absolutely unique type of authentication.
At first, when I've tried to connect to SQL Server using "TestUser2" I've got an error:

Then I've tried to go to additional connection "Options":

And explicitly specified the name of the database I want to connect to:

That made the trick. I was be able to connect and see my database:

Wow! I can see ONLY and ONLY one database I'm assigned to.

I've tried to see what else I can see:

SELECT * FROM sys.databases;

And here is the list of databases I can really see:

Along with "TestDB" I'm exposed to "master" and "tempdb".
I can see data from tables in "master", I can create "#Temp" tables, but can write directly only to "TestDB".

Now, here is how to set up "DATABASE" authenticated user. It is pretty simple, just specify a user name in current database with a password:

USE TestDB;
GO
CREATE USER [TestUser2]  WITH PASSWORD = 'TestUser2';
GO

 Actually, that is not only it. Before you be able to setup database authenticated user you have to switch your database into Partial Containment mode and to do so, you have to enable it on the server level first:

USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0 ;
GO
RECONFIGURE
GO
ALTER DATABASE TestDB SET CONTAINMENT = PARTIAL;
GO

Looks cool?
Yep, it is really cool that you can COMPLETELY separate a user form ANY OTHER databases on your server!!!

BUT. There is always "BUT". There no "Silver Bullet" nor "Free Cheese".
If you use Partially contained databases cannot use replication, change data capture, or change tracking and some other features.

The good news: you are good to go with AlwaysOn.

Conclusion:

Because Contained Database Users can easily coexist with all other types of users and they are highly restricted within boundaries of one database, since SQL Server 20014, it can be very good addition to your tool set of other security features.

MSDN links for further research:

Contained Databases: https://msdn.microsoft.com/en-us/library/ff929071.aspx
Contained Database Users: https://msdn.microsoft.com/en-us/library/ff929188.aspx
Create a Database User: https://msdn.microsoft.com/en-us/library/aa337545.aspx