Auto Fix Orphaned Users

By on October 22nd, 2013

Auto Fix Orphaned Users.

When you move your database to any higher version, Logins and Users are not sync.

This type of problem comes while the migraion, SQL calls that all users as Orphaned Users and we have to link it manually.

we have the script bellow to link it manually.

USE ADVENTUREWORKS2012
GO
DECLARE @Users TABLE
    (
         Id    INT IDENTITY(1, 1),
         Users VARCHAR(250)
    )
DECLARE @Rows     INT,
        @Username VARCHAR(250)

INSERT INTO @Users

SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN ( 'guest', 'sa', 'dbo', 'public',
                           'sys', 'INFORMATION_SCHEMA' )
SET @Rows = @@ROWCOUNT
WHILE ( @Rows > 0 )
  BEGIN
      SELECT @Username = Users
      FROM   @Users
      WHERE  Id = @Rows

      EXEC Sp_change_users_login 'Auto_Fix', @Username, NULL, @Username
	  SELECT @Username
	  SET @Rows = @Rows - 1
  END

Thanks & Regards,
Nirav Gajjar

Categorized under: SQL Server, TSQL.
Tagged with: no tags.