Database User not reported as orphaned after Windows login dropped

Question

I have dropped a SQL Server Windows Login. I then ran the below code to check for orphaned database users. However, the database user corresponding to the dropped Windows login does not appear as an orphaned user.

Why would this be?

EXEC sp_change_users_login @Action = 'Report';

Answer 1

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:

SELECT p.name 
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
AND NOT EXISTS
(
  SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
);

If you need to do this for all databases, you can generate this dynamically, e.g.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT ''' + db.name + ''',p.name
  FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
  WHERE [type] IN (N''U'', N''G'')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
  );'
FROM sys.databases AS db
WHERE [state] = 0;

EXEC sp_executesql @sql;

Answer 2

That stored procedure sp_change_users_login only reports on SQL users, not Windows users.

Here is the actual reporting query that the stored procedure uses (you can get the stored procedure text with sp_helptext 'sp_change_users_login'):

select UserName = name, UserSID = sid from sysusers
where issqluser = 1 
and   (sid is not null and sid <> 0x0)
and   (len(sid) <= 16)
and   suser_sname(sid) is null
order by name

Note the first predicate statement: where issqluser = 1. In the sys.sysusers view the issqluser column indicates 1 if the account is a SQL user. Windows users don't meet that criteria, therefore they aren't being reported on.