This recipe shows how you can remap orphaned database users to valid logins.
Let's create an orphaned user for us to use in this recipe. Open SQL Server Management Studio and execute the following T-SQL statements:
USE [master] GO CREATE LOGIN [baymax] WITH PASSWORD=N'P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [AdventureWorks2014] GO CREATE USER [baymax] FOR LOGIN [baymax] GO USE [master] GO DROP LOGIN [baymax] GO -- create another SQL login -- note this will generate a -- different security ID (SID) CREATE LOGIN [baymax] WITH PASSWORD=N'P@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
This code has created an orphaned user called baymax in the AdventureWorks2014 database. Although we have recreated a login with the same name, this would generate a different security ID and leave the database user orphaned.