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
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes