Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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';
share|improve this question
up vote 5 down vote accepted

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

FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
  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.


SELECT @sql += N'SELECT ''' + + ''',
  FROM ' + QUOTENAME( + '.sys.database_principals AS p
  WHERE [type] IN (N''U'', N''G'')
    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;
share|improve this answer

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.

share|improve this answer
Yep. And ugh, it uses sysusers. Bad Microsoft. – Aaron Bertrand Aug 19 '14 at 16:17

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.