SQL Server 2005 Security – Discrepancy Between System Catalog View and sp_change_users_login

Securitysql-server-2005

I want to check for orphaned users on a database. To that end, I use the following query on my SQL Server 2005 instance to get all users and their accompanying logins for a specific database (say MyDB):

SELECT  U.name AS UserName
        ,U.type_desc
        ,U.create_date
        ,U.modify_date
        ,U.default_schema_name
        ,P.name AS LoginName
        ,P.is_disabled
        ,P.type_desc
        ,P.default_database_name
        ,P.create_date
        ,P.modify_date
FROM    MyDB.sys.database_principals AS U
LEFT OUTER JOIN master.sys.server_principals AS P ON U.sid = P.sid
WHERE   U.type IN ('G', 'S', 'U')
ORDER BY UserName;

When I compare the results of this query with the results of USE MyDB; EXEC sp_change_users_login @Action = 'Report'; things do not seem to add up. The first query gives me 12 users of which 6 do not have logins; but the call to sp_change_users_login returns only 1 orphaned user.

Even if I take users like guest, INFORMATION_SCHEMA and sys out of the equation, there are still two users without a login attached in my first query that do not show up in the call to sp_change_users_login. What am I missing?

EDIT
I have changed use of the deprecated sysuser system table to the database_principals system view, according to the handy MS Mapping System Tables to System Views article (and Aaron of course).

Best Answer

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014.

Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no result if orphan user is NT User instead of SQL User. 

  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

According to BOL "sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN."

Best script to find orphan users against all databases on an instance is already provided by "Mark Storey-Smith"

Find orphaned users

Related Question