Sql-server – Database User not reported as orphaned after Windows login dropped

loginssql serverusers

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';

Best Answer

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;
Related Question