remap db user to server login

When you restore a 2005+ database from one instance to another, the database users are preserved in the database, but now they are linked to the GUID of the login on the originating instance.   The login may exist by the same name on the target server, but since the principal GUIDs are different the user is not linked to that login.  One possibility is to drop and recreate the user and/or login, but this is very destructive in the case that there are complex permissions involved for the user in the DB.

   DECLARE @tab TABLE ( id int identity(1,1), uname sysname )

   DECLARE @id int
         , @uname sysname
         , @sql nvarchar(4000)

     INSERT INTO @tab ( uname )
          SELECT name
            FROM sysusers 
           WHERE issqluser = 1 
             AND hasdbaccess = 1 
             AND name != 'dbo'

          SELECT @id = max(id) from @tab

   WHILE @id > 0
    BEGIN
          SELECT @uname = uname 
            FROM @tab 
           WHERE id = @id

      EXEC sp_change_users_login @action = 'Update_One', @UserNamePattern = @uname, @LoginName = @uname

      SET @id = @id - 1
    END

 Used to use sp_change_users_login, but since that is now deprecated, it is recommended to use the following: