I’ve seen the odd question on forums concerning users without login. These "users without logins" are sometimes confused with "orphaned users", however
these two types of users are quite different. A user without login is a special type of user that has deliberately been set up without an associated
login. In contrast an orphaned user is one where the user is not currently associated with a login, most commonly because a database has been
restored from another server and the association with the login has either been lost or the login does not exist on the new server. Normally
when orphaned users are discovered they are just connected back to their associated logins. However a user without login is one that does not
have and cannot have an associated login. While this might not sound like a very useful type of user (and indeed in my experience they’re not that commonly used)
they can be used in conjunction with impersonation from another login. Sometimes they are used as a replacement for application roles.
Correcting Orphaned Users
To reconnect an orphaned user with its login you can run the following SQL (assuming that both the login and user are called TestUser) :
ALTER USER TestUser WITH LOGIN=TestUser
If the login doesn’t exist then you’ll need to create it first.
You can also use the stored procedure sp_change_users_login
to reconnect the user with the login, however as
that is now deprecated the above SQL is preferred.
If the user is not an orphaned user but is actually a user without login then running this SQL will generate the following error :
Msg 33016, Level 16, State 1, Line 1
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.
This is because it’s not possible to convert from a user without login to a user that is connected to a login, so if that is what
is required then the user will have to be dropped and recreated, and permissions reapplied.
Identifying Users Without Login and Orphaned Users
The SQL Server Management Studio GUI does not distinguish between Users Without Login and Orphaned Users, at least not in the User Properties window. If you go to
Databases -> your DB -> Security -> Users in Object Explorer and right click on either a user without login or an orphaned user, the user will appear as follows :
In SQL Server 2012 the window is slightly different, but again it doesn't distinguish between a user without login or an orphaned user :
The only way (that I know of) to identify whether a user is a user without login is to look at the SID of the user. Users created
without logins have a longer SID, usually of length 28. Other SQL Server users (whether orphaned or not) usually have SIDs of length 16. The
query below adds an extra column to the catalog view sys.database_principals which indicates whether a user is a user without login, in
which case the new column will be set to value 1.
-- SQL to run to identify users without login :
SELECT CASE WHEN DATALENGTH(sid) = 28
AND type = 'S'
-- only want SQL users
AND principal_id > 4 -- ignore built in users
THEN 1 ELSE 0 END AS is_user_without_login,*