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
        ,P.name AS LoginName
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?

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).

  • 2
    Why are you using sysusers, which is deprecated? You should be using server_principals and database_principals... Commented Nov 28, 2014 at 12:42
  • Excellent catch, thanks! I adapted the query to use database_principals instead of sysusers, but unfortunately this still shows the exact same discrepancies.
    – user5147
    Commented Nov 28, 2014 at 13:06

1 Answer 1


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

  • Great answer, thanks! I had not thought that this was due to sp_change_users_login - that one floats all over the internet when the talk is about orphaned users (see here for example).
    – user5147
    Commented Nov 28, 2014 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.