I am grouping here two topics (orphaned users and loginless users) that are actually very different, but I have often seen confusion between them, so I am covering them together in an attempt to dispel that confusion.
In a previous discussion of logins and users, I pointed out that the way a login gets mapped to users in databases is via its SID value. This mapping is a one to many mapping – a login maps to several users (each one in a different database), but a user maps back to a single login. When a Windows login is created, its SID value is taken to be the SID of the corresponding Windows principal, but for SQL Server specific logins the SID values are generated at creation time, unless they are explicitly provided via the SID clause of CREATE LOGIN. The SID value represents the identity of the login and is invariant for the life of the login – this is why the SID clause is not available for ALTER LOGIN.
The SID link between a login and a user can be severed when databases are moved from one server to another – this is because on the new server, there may be no login having the same SID as that user. When such a situation happens, we say that the user was orphaned – it becomes an orphan user. Orphan users can also result from the deletion of a login – in previous versions, SQL Server attempted to warn about the existence of corresponding users, but even that check would fail to detect users in databases that were offline, so SQL Server 2005 no longer performs it (it was also an expensive check, as it required accessing each database). The bottom line is that orphaned users can appear in various ways and we have to either fix them when they occur or we have to prevent them from occurring at all. The classic method for restoring the SID link was to use the sp_change_users_login procedure, but starting with SQL Server 2005 SP2, a new LOGIN clause of ALTER USER has also been made available, which helps fix Windows users in addition to SQL users – a feature that sp_change_users_login was lacking. These methods will fix the SID of a user to match that of a login, effectively remapping the user to match the login. For the preventive approach, the main tool we have available is to create logins with an explicit SID value – this will help when moving a database between two or more servers, by allowing us to create in advance SQL Server logins that not only have the same name, but the same SID value as well. This will also help with recreating a login that was inadvertently dropped.
Some people seem to think that having the possibility to change a login’s SID to match that of a user would be a great feature. That is not true. Changing a login’s SID would be wrong because it would attack the problem on the wrong side of the login-user relationship. When fixing the user SID, only that SID needs to be changed and this would be a database scoped operation, but when fixing a login SID, all corresponding user SIDs would have to be changed as well, otherwise we would generate more orphaned users instead of fixing them – and fixing all users mapped to a login isn’t a task that can be easily performed, because it would require the availability of all databases in which the login is mapped – something that cannot be guaranteed. There is a good reason why this issue is referred to as orphaned users and not as childless logins.
So, orphaned users are an issue that normally requires some fixing, when it occurs. Loginless users, on the other hand, are an intentional construct. They are created using the WITHOUT LOGIN clause of CREATE USER and they represent standalone users that cannot map to a login (they have special SID values preventing this), so you cannot connect to a database as a loginless user, although you can impersonate one via EXECUTE AS. A loginless user is valuable in many situations where an application needs to have users that don’t necessarily correspond to a human user. For example, an application may be in need to create objects from time to time and to designate an owner for them – it could do this in the caller’s context, but that would mean the caller would end up owning objects he may not be aware of – it is better for the application to designate a loginless user as the owner of those objects. One of my favorite uses of loginless users is to break ownership chains (see previous discussion) by assigning different loginless users as owners. Raul has a lengthier post on the topic of loginless users that offers further examples of use, including some TSQL sample code.