Fix Orphaned Users in SQL Server by serverku

You may aware of the orphaned users and it is experienced when restore a database backup to another server with logins which means the database user restored in a system which does not have associated valid logins. After restoring a database backup we can fix those orphaned users with sp_change_users_login. Let us see a small example to fix orphaned database users.

-- At source server
USE [master]
GO
-- Creating a test login
CREATE LOGIN [testlogin] WITH PASSWORD=N'testlogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DemoDB]
GO
-- Creating a database user for login testlogin created 
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
-- Assigning a database role to testlogin user created
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

USE master
GO
-- Taking a database backup at source
BACKUP DATABASE [DemoDB] 
TO DISK = 'D:\DemoDB_full_20130913.bak'


-- At destination server
USE Master
GO
-- Restoring a database backup at source
RESTORE DATABASE [DemoDB] 
FROM DISK = 'D:\DemoDB_full_20130913.bak'
WITH replace,
MOVE 'DemoDB' TO 'D:\Data\DemoDB_data.mdf',
MOVE 'DemoDB_log' TO 'D:\Log\DemoDB_log.ldf'


USE DemoDB
GO
EXEC sp_change_users_login 'report'
GO

/*
UserName UserSID
----------------------------------------------
testlogin 0xFD13E649B5EF75469A22D598C8E0790D
*/

-- Mapping a user to new sql login
USE DemoDB
GO
CREATE LOGIN testlogin WITH PASSWORD = 'testlogin'
GO
EXEC sp_change_users_login 'Update_One','testlogin','testlogin'
GO

-- Automatically mapping a user to login, creating a new login if not exists
USE DemoDB
GO
EXEC sp_change_users_login 'Auto_Fix','testlogin',NULL,'testlogin'
GO

/* Output
-- If login does not exists
Barring a conflict, the row for user 'testlogin' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

-- If login already exists
The row for user 'testlogin' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
*/

You can fix all users at one shot using the following script,

USE [DemoDB]
GO

CREATE TABLE #OrphanedUsers
    (
         Id    INT IDENTITY(1, 1),
         UserName VARCHAR(250)
    )

DECLARE @i INT, 
  @Total     INT,
        @User VARCHAR(250)

INSERT INTO #OrphanedUsers (UserName)
SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN 
  ( 'guest', 'sa', 'dbo', 'public',
          'sys', 'INFORMATION_SCHEMA' )

SET @Total = @@ROWCOUNT
SET @i = 1

WHILE ( @i <= @Total)
  BEGIN
      SELECT @User = UserName
      FROM   #OrphanedUsers
      WHERE  Id = @i

      EXEC sp_change_users_login 'Auto_Fix', @User, NULL, @User

      SET @i = @i + 1
  END

DROP TABLE #OrphanedUsers

Hope you enjoyed this small example and would like to you share ideas for same.
Stay tuned for more!