Fixing orphaned users

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

Getting ready

Let us create an orphaned user to use in this recipe. Open up SQL Server Management Studio, and execute the following T-SQL statements:

USE [master]
GO
CREATE LOGIN [marymargaret] 
WITH PASSWORD=N'P@ssword', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
USE [AdventureWorks2008R2]
GO
CREATE USER [marymargaret] 
FOR LOGIN [marymargaret]
GO
USE [master]
GO
DROP LOGIN [marymargaret]
GO
-- create another login, this will generate a 
-- different SID
CREATE LOGIN [marymargaret] 
WITH PASSWORD=N'P@ssword', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF

This code has created an orphaned user called marymargaret in the AdventureWorks2008R2 database. Although we have recreated a login with the same name, this would generate a different Security ID (SID), thus leaving the database user orphaned.

How to do it...

1. Open the PowerShell console by going...