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 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.
How to do it...
Let's list the steps to fix orphaned users:
Open PowerShell ISE as an...