In this post I'd like to talk about some basic SQL Server security concepts. SQL Server has a less common design that can confuse users familiar with the security features of other software products, such as Microsoft Windows OS; in particular, the difference between logins and users is one that seems to invariably confuse most new SQL Server users.
The first important thing that needs to be understood about SQL Server security is that there are two security realms involved - the server and the database. The server realm encompasses multiple database realms. All work is done in the context of some database, but to get to do the work, one needs to first have access to the server and then to have access to the database.
Access to the server is granted via logins. There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins. Windows authenticated logins can either be logins mapped to Windows users or logins mapped to Windows groups. So, to be able to connect to the server, one must have access via one of these types or logins - logins provide access to the server realm.
But logins are not enough, because work is usually done in a database and databases are separate realms. Access to databases is granted via users.
Important Note: When describing a SQL Server scenario, terms like logins and users should not be used interchangeably - a login is a different concept than a user.
Users are mapped to logins and the mapping is expressed by the SID property of logins and users. A login maps to a user in a database if their SID values are identical. Depending on the type of login, we can therefore have a categorization of users that mimics the above categorization for logins; so, we have SQL users and Windows users and the latter category consists of users mapped to Windows user logins and of users mapped to Windows group logins.
Let's take a step back for a quick overview: a login provides access to the server and to further get access to a database, a user mapped to the login must exist in the database. It is important to know the difference between these two concepts of login and user and to not use these terms interchangeably.
The existence of databases as a separate realm has the advantage of allowing a database to be detached from a server instance and to be attached to another one. The operation is not without issues, as users may need to be remapped to logins in the new instance and this is a manual operation - it can be performed using sp_change_users_login or, starting with SQL Server 2005 SP2, by using a new clause of the ALTER USER statement.
Note: Users that are not mapped to a login as a result of a database move are known as orphaned users.
Another advantage is that databases as separate entities allow a better separation of distinct applications - applications can each be placed in their own database, making it harder to accidentally or maliciously access data under the control of another application.
This separation has an impact on the authorization model of SQL Server as well, as it may be expected. Permissions can be granted at both scopes: server and database. Permissions granted at server scope take effect regardless of what is the current database, but permissions granted at database scope are used only while working in that database. Server level permissions are the permissions that are assignable to logins, and database level permissions are the permissions assignable to users. To help with the management of permissions, some other entities than the logins and users we discussed so far can be used as the grantees of a permission. Collectively, in SQL Server, the entities that can be granted a permission are referred to as principals. Principals are separated into server principals and database principals according to their scope.
Important Note: While the logins discussed so far grant access to the server and thus are having a role in the authentication process, the other server principals are only used for permission management and do not help with server authentication.
Examples of server principals besides the logins discussed previously are server roles and logins mapped to certificate or asymmetric keys. Examples of database principals are database roles (fixed and flexible), application roles, users mapped to certificate or asymmetric keys, and loginless users. All of these help with the assignment of the permissions. Loginless users (created using CREATE USER ... WITHOUT LOGIN) are a special case because they can be impersonated (application roles are similar, but terminology differs - approles are set, while loginless users are impersonated).
The last concept I want to discuss here is that of execution context. The execution context is how SQL Server keeps track of who you are. As it may be expected already, the execution context is formed of a server execution context and a database execution context - these are referred to as login token and user token. The login-user token pair determines who you are in the eyes of the system. The context is initially determined upon login and changes when you switch to a different database or when an impersonation takes place. For more information on the execution context and on impersonation mechanisms, which are topics that extend well beyond the scope of this post, you can have a look at my presentation from http://cmcgc.com/Media/WMP/261115/, its relevant demo, and this post.