locked
sp_change_users_login not working as expected RRS feed

  • Question

  • Why would sp_change_users_login 'report' show different results than doing a join?

    select *
    from sys.sysusers L
    inner join sys.syslogins LG ON L.SID = LG.SID

    I have a login that is omitted from the query with the join, but is not showing up in the sp_change_users_login 'report'.  Why would that be?  I know that the user is not a login (it is a Windows Login) in the SQL Server and the SID in the User Table does not exist in the sys.syslogins.

    Thanks.


    Ben Miller
    Monday, September 28, 2009 10:32 PM

Answers

  • sp_change_users_login does not handle logins that are mapped to Windows users or groups.  Therefore, the report parameter is going to exclude them.  It will only return the users that can be dealt with by the procedure.  Since you referenced sys.sysusers, it tells me you are running SQL Server 2005 or higher, in which case you need to stop using sp_change_users_login.  This stored procedure is deprecated and will be removed from the product, because ALTER USER does everything this procedure used to as well as being able to handle logins mapped to Windows users or groups.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, September 29, 2009 4:37 AM

All replies

  • The two queries are doing different things and they are not supposed to be equivalent.

    First run:

    use master
    exec sp_helptext sp_change_users_login
     
    Check the section when the param is 'report':

            -- GENERATE REPORT --
            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

    Suser_sname returns the login name associated with a SID.

    Now compare it to your query and see the difference.  

    Thanks,

    Varsham Papikian, New England SQL Server User Group Executive Board, USA
    New England SQL Server User Group; My LinkedIn page
    Please remember to click the "Mark as Answer" button if a post helps you!
    Tuesday, September 29, 2009 4:24 AM
  • sp_change_users_login does not handle logins that are mapped to Windows users or groups.  Therefore, the report parameter is going to exclude them.  It will only return the users that can be dealt with by the procedure.  Since you referenced sys.sysusers, it tells me you are running SQL Server 2005 or higher, in which case you need to stop using sp_change_users_login.  This stored procedure is deprecated and will be removed from the product, because ALTER USER does everything this procedure used to as well as being able to handle logins mapped to Windows users or groups.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, September 29, 2009 4:37 AM