Hi Orlando,

Nice article. However, I'm going to disagree with your method of looking at the length of the sid to determine whether this is a loginless user, or an orphaned user.

In SQL 2012, the sys.database_principals catalog view was expanded to carry a few additional columns, including the authorization_type (and corresponding authorization_type_desc). If authorization_type = 0 (for authorization_type_desc = "NONE"), then this is a loginless user.

Prior to SQL 2012, these columns did not exist. To check for loginless users, perform a LEFT JOIN to sys.sql_logins, which exists since 2005.

Here's a quick example:

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

CREATE LOGIN monkey WITH PASSWORD = 'monkey';

GO

CREATE USER monkey FROM LOGIN monkey;

CREATE USER donkey WITHOUT LOGIN;

-- SQL 2012 has the authentication_type & authentication_type_desc columns.

SELECT name,

type,

type_desc,

authentication_type,

authentication_type_desc

FROM sys.database_principals

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

-- prior to SQL 2012, you can left join to sys.sql_logins

SELECT sp.name,

sp.type,

sp.type_desc,

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

WHEN sp.type = 'S' THEN 2

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

END AS authentication_type, -- ignoring contained database users

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

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

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

END AS authentication_type_desc

FROM sys.database_principals sp

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

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

GO

DROP USER monkey;

DROP USER donkey;

DROP LOGIN monkey;

Reference for sys.sql_logins: http://msdn.microsoft.com/en-us/library/ms174355.aspx