Basic SQL Server Security concepts – logins, users, and principals


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.

Comments (14)

  1. psuppes says:

    Learning to differentiate between users and logins is an important part of the SQL Server security concepts. Thanks Laurentiu. My comment is the blog does commingle the concepts of a server and a server instance.  As examples, in the second paragraph the blog divides the world into two security realms – the server itself and the database within a server instance.  But at the beginning of the next paragraph starts with “Access to the server is granted via logins”… I believe here we’re talking about access to the server instance.

    You may want to add an additional note to help the reader realize the difference between the use of the terms “server” and “server instance.”  Like the terms login and user, the difference is becoming more important.

  2. lcris says:

    You are correct, "server" in my posts is identical to "SQL Server instance".

    I am not sure about your use of "server instance", however – this is an ambiguous term that I would not use except as a shortcut for "SQL Server instance". Each SQL Server instance is a separate server application with its own security settings (even if some such instances may be instances of the same SQL Server application), so I find the term "server" appropriate and unambiguous in the way I use it.

  3. Luan.Moreno Brazil says:

    Laurentiu Cristofor's,

    First congratulations for the post. This security conceps always blurry and my mind,Now i understanding the divisions of security in SQL Server in a simple form

    Thanks!

  4. geos says:

    great article! unfortunately the presentation is not available (actually all presentations on the page mentioned above). is there any chance to get them from another place?

    thank you,

    geos

  5. Maryam Allahverdi says:

    very good and helpful post

    thanks alot

  6. lcris says:

    @geos – Sorry, I don't know what happened with the hosting of those presentations. I created a post with the content of my presentation here: blogs.msdn.com/…/sql-server-2005-execution-context.aspx

    @Maryam – Thanks!

  7. Ross says:

    Very Good.  I never understood the philosophy behine the design.  No I do. I wish all written documentation was this good!  

  8. Fedor says:

    Nice article. My questions is about loginless users. How does a loginless user allow to enter the server, since it is not mapped to a server login?

  9. lcris says:

    Loginless users are only used for managing permissions at database level – the fact that they cannot be used to gain access to the server is their distinguishing feature. I wrote a bit more about them in this post:

    blogs.msdn.com/…/basic-sql-server-security-concepts-sids-orphaned-users-and-loginless-users.aspx, which also includes links to some examples of their use.

  10. phani says:

    Although same matter is repeated much…concept was made simple. Got good idea on logins and user.

    Thanks Cris  

  11. Vishnu says:

    Great article – thanks for clarifying the concepts

  12. Marian says:

    Clear description how security works on SQL; now I finally understand why creating logins for Windows groups is not sufficient to access individual databases.

    Thanks!

  13. David N Nguyen says:

    Without a login, an account cannot access a server. Without user, account cannot access database.

    However, I notice that once read access has been granted to an AD group account, then all members of this AD group can access this server and its related database even though its members DO NOT HAVE  either a windows or SQL logins.

  14. lcris says:

    @David Nguyen: AD groups are special, but they still don't escape this model. SQL Server may even create individual user accounts if users in a group create objects in a database. This is called "implicit user creation".