Orphan users

When a new database is refreshed from lets say Prod server (source) to Dev server (destination), all database users/roles from Prod (mind you, only the db users not the logins) will be copied over to Dev, but the challenge is, their respective logins may not be available on the dev servers and this results in orphaned users scenario. And the development team will complain they are not able to access the database.

Here are the steps to fix this issue.

On destination database run the below script to identify the orphaned users

use <refreshed database>
go
sp_change_users_login 'report' 

This will list the orphaned users in the database. Once you get the list of users, next task is to create their corresponding logins (these logins you should get from the source server) on the destination server.

Run the script in the below mentioned link on the source server to script out the logins. Identify the logins which is to be copied over to destination and apply the same on the destination server.

https://support.microsoft.com/en-in/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

After you do that, run the below script to ensure their sids are remapped. 

--Query to fix all orphan users on a database

Use <DB Name>
go
CREATE PROCEDURE #P_DBA_FIX_ORPHAN_USERS
AS
DECLARE @ErrorMsg varchar(255)
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser =1 and (sid IS NOT NULL AND sid <> 0x0)
        AND suser_sname (sid) IS NULL ORDER BY name

IF @@ERROR <> 0
 BEGIN
SET @ErrorMsg = 'Error while selecting the data from SYSUSERS Table'
RAISERROR(@ErrorMsg, 16, 1)
RETURN(16)
 END

OPEN fixusers
FETCH NEXT FROM fixusers INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC sp_change_users_login 'update_one',@username,@username
  FETCH NEXT FROM fixusers INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers

END
go

exec #P_DBA_FIX_ORPHAN_USERS
GO

drop PROCEDURE #P_DBA_FIX_ORPHAN_USERS
go