Thanks for having a look Wayne and for the nod. Also, congrats on recently becoming an MCM! That's an amazing accomplishment.

I agree that using sys.sql_logins is a cleaner way to differentiate a Loginless User from a User linked to a Login but what I found when researching how to do this in my environment is that using the instance-level views only held up in the case when the User had not yet been orphaned. The scenario I tried to address in the article was to find Users that were presently orphaned meaning I needed a way to omit Loginless Users from the resultset.

Consider this test case to see why I went after the SID:

USE YourDatabaseName;

GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'monkey')

CREATE LOGIN monkey WITH PASSWORD = 'monkey', CHECK_POLICY = OFF;

GO

CREATE USER monkey FROM LOGIN monkey;

CREATE USER donkey WITHOUT LOGIN;

-- run the query you showed as "prior to SQL 2012, you can left join to sys.sql_logins" to see what types of users these are

SELECT dp.name,

dp.type,

dp.type_desc,

CASE WHEN sl.name IS NULL AND dp.type = 'S' THEN 0

WHEN dp.type = 'S' THEN 2

WHEN dp.type LIKE '[UG]' THEN 3

END AS authentication_type, -- ignoring contained database users

CASE WHEN sl.name IS NULL AND dp.type = 'S' THEN 'NONE'

WHEN dp.type = 'S' THEN 'INSTANCE'

WHEN dp.type LIKE '[UG]' THEN 'WINDOWS'

END AS authentication_type_desc

FROM sys.database_principals dp

LEFT JOIN sys.sql_logins sl ON dp.sid = sl.sid

WHERE dp.name IN ('donkey', 'monkey');

-- looks good, we can tell that monkey is a user with a Login and donkey

-- is a Loginless User

-- now let's orphan monkey

DROP LOGIN monkey;

-- run the same query again

SELECT dp.name,

dp.type,

dp.type_desc,

CASE WHEN sl.name IS NULL AND dp.type = 'S' THEN 0

WHEN dp.type = 'S' THEN 2

WHEN dp.type LIKE '[UG]' THEN 3

END AS authentication_type, -- ignoring contained database users

CASE WHEN sl.name IS NULL AND dp.type = 'S' THEN 'NONE'

WHEN dp.type = 'S' THEN 'INSTANCE'

WHEN dp.type LIKE '[UG]' THEN 'WINDOWS'

END AS authentication_type_desc

FROM sys.database_principals dp

LEFT JOIN sys.sql_logins sl ON dp.sid = sl.sid

WHERE dp.name IN ('donkey', 'monkey');

-- according to the query monkey and donkey both look to be orphaned however we know that

-- donkey is a loginless user and monkey used to have a login but was recently orphaned

DROP USER monkey;

DROP USER donkey;

GO

What I found was that as soon as the User was orphaned, i.e. when the Login was dropped, the metadata changed to my disadvantage making it impossible to differentiate a Loginless User from a regular, now-orphaned User:

This behavior is was what pushed me towards deriving the information based on the length of the SID.

I do like that there is a cleaner experience in SQL 2012. Thank you for bringing these new columns to my attention. I must admit that at the current shop I work almost exclusively with 2008 R2 with only some 2005 and a couple 2012 instances coming online soon so the method is targeted at 2005 and 2008 R2. I tested the method in the article on SQL 2012 and it seemed to hold up but if and when I am in a shop with only SQL 2012 I would probably switch to leverage the new columns. Here is what I came up with, adapted from what you shared:

-- SQL 2012

SELECT dp.name,

dp.type,

dp.type_desc,

dp.authentication_type,

dp.authentication_type_desc,

sp.name

FROM sys.database_principals dp

LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid

WHERE dp.authentication_type_desc = N'INSTANCE'

AND sp.name IS NULL;

I tested the above query on regular and partially contained databases and it seemed to work well, although I did not do anything that could be considered exhaustive. The thing that seems to make this method hold up is that after dropping the Login the value of authentication_type_desc remains equal to 'INSTANCE'.