Orphaned Users vs Users Without Logins

Category : Blogs Published : November 18, 2013 User Rating : 5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
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 :
Database User Without Login - SQL Server 2008 R2

In SQL Server 2012 the window is slightly different, but again it doesn't distinguish between a user without login or an orphaned user :
Database User Without Login - SQL Server 2012

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,*
FROM sys.database_principals

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Orphaned Users vs Users Without Logins.aspx

Keywords

SQL, blogs, users, logins, orphaned, password


Comments
Post by pawel on Tue 23 Aug 2016 12:00. Report Inappropriate Post

Very helpful, thank you! :)
Post by Curt on Thu 20 Apr 2017 13:43. Report Inappropriate Post

Yes, thank you! Very clear explanation.
Post by Frank on Thu 19 Apr 2018 18:04. Report Inappropriate Post

This is a lot better.
--Try this one out and let me know how it works.

SELECT A.name, Case
When (a.name = b.name and a.sid <> b.sid and B.sid is not Null) then 'Orphaned User'
When (a.name = b.name and a.sid = b.sid and B.sid is not Null) then 'SQL Login'
Else 'Users Without Login' END as 'Login Type'
FROM sys.database_principals a
Left Join Master.[sys].[syslogins] b
on a.sid = b.sid or a.name = b.name
Where type = 'S' AND principal_id > 4
Post by Jin on Tue 17 Jul 2018 13:13. Report Inappropriate Post

Thanks for this post. An orphaned db user should have an value of "INSTANCE" in the column "authentication_type_desc" in the sys.database_principals, while a db user without login should have a value "NONE". Hope this helps.

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered