sp_change_users_login; Orphan Users

While we are restoring database from prdouction server to our local server there may be some orphaned users in the database.So we can findout those users by running the below query. Use DatabaseName go sp_change_users_login 'report' You will get UserNames and their uniqueidentifier numbers.Then you can update the orphaned users. sp_change_users_login 'update_one','LoginName','loginName' Eg:sp_change_users_login 'update_one','ramesh','ramesh'

use DatabaseNameHere
GO
EXEC sp_change_users_login 'report'
go
--Pass the values that comes as users from above 'report' list
exec sp_change_users_login 'update_one','UserName','UserName'

The below commands from dbatools

cls
Get-DbaDbRestoreHistory `
-SqlInstance SQLServerInstanceName `
-Database DatabaseName `
-Since '2022-10-17 00:00:00' `
-Verbose

Get-DbaDbOrphanUser `
-SqlInstance SQLServerInstanceName `
-Database DatabaseName `
-Verbose

Repair-DbaDbOrphanUser `
-SqlInstance SQLServerInstanceName `
-Database DatabaseName `
-Verbose

I came across a senario where Expot-dbalogin not worked on a specific named instance. So I am unable to create to logins specific to a particular database. There is no database option in Copy-dbalogin where as it appears in Export-dbalogin. In that case we can use below query.

$Orphan = Get-DbaDbOrphanUser -SqlInstance DestinationServer -Database DestinationDatabase
Copy-DbaLogin -Source SourceServer -Login $Orphan.User -Destination DestinationServer -Verbose

Below is the sample for above example command

$Orphan = Get-DbaDbOrphanUser -SqlInstance RAMESH\LAKSHMI -Database AdventureWorksdw2017
Copy-DbaLogin -Source RAMESH\UAT -Login $Orphan.User -Destination RAMESH\LAKSHMI -Verbose