Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

2 Answers 2

up vote 4 down vote accepted

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;
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 at 16:17

Your Answer

 
discard

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.