SQL Server 2014 with PowerShell v5 Cookbook

Fixing orphaned users

This recipe shows how you can remap orphaned database users to valid logins.

Getting ready

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 ...