USE master
GO

sp_configure 'updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

IF OBJECT_ID('sp_ABFixUserLoginLinks') IS NOT NULL
        DROP PROCEDURE sp_ABFixUserLoginLinks
GO

CREATE PROCEDURE sp_ABFixUserLoginLinks  (@AddLogins int = 1,
                                          @NewPassword varchar(30) = null)
/* Name:    sp_ABFixUserLoginLinks
 *
 * Purpose: This procedure will attempt to fix the broken links caused by a DUMP on one
 *          system and a LOAD on another.  It updates the suid in the restored database
 *          to match the names on syslogins of the restored server
 *
 * NOTE:    This procedure will NOT fix aliases, since these depend on the suid of the 
 *          source system's master..syslogins and is not available.
 *          
 *
 *          
 *          
 *          
 *        
 * Written By:  Vince Iacoboni   02/11/1998
 *              vince.iacoboni@btalexbrown.com
 *              vbi@writeme.com
 *
 * Modification History: 
 *
 *
 *
 */


AS

DECLARE @UserName varchar(30), 
        @DbName varchar(30)

SELECT  @DbName = db_name()

SELECT  UserName = name,
        Uid uid,
        Suid suid
  INTO  #NoLogins
  FROM  sysusers
  WHERE name NOT IN (SELECT name FROM master..syslogins)
    AND suid > 0

WHILE EXISTS (SELECT * FROM #NoLogins)
  BEGIN
     SET ROWCOUNT 1

        SELECT @UserName = UserName
          FROM #NoLogins

        DELETE #NoLogins

     SET ROWCOUNT 0

     IF @AddLogins = 1 
        EXEC sp_addlogin @UserName, @NewPassword, @DbName
     ELSE
        EXEC sp_dropuser @UserName
  END         

DROP TABLE #NoLogin

UPDATE  sysusers
  SET   suid = l.suid
  FROM  master..syslogins l
  WHERE sysusers.name = l.name
    AND sysusers.suid > 0

GO
sp_configure 'updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO