To illustrate this I'll run following query in my test box:
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.
I'll get following error:
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:
Create user with NONE authentication is pretty simple, you just specify that you want to create a user with no SQL Server login:
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:
And here is the list of databases I can really see:
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:
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:
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