Copy-DbaLogin - Update changed SQL logins in place instead of dropping them #10772

Summarize Functionality

Split out of #10726, where it was item 3 of the suggested order. Items 1 and 2 (Agent jobs) are done in #10767 and #10768.

Copy-DbaLogin handles a login that already exists on the destination in one of two ways:

• Without -Force it skips the login. A password changed on the source never reaches the destination.
• With -Force it drops and recreates the login. Before the drop it makes sa the owner of every database and Agent job the login owns, and it doesn't change them back. A login with open sessions can only be dropped with -KillActiveConnection.

Sync-DbaAvailabilityGroup calls Copy-DbaLogin -Force:$Force, so an AG sync gets one of the two: stale passwords on the secondaries, or every login dropped and its ownerships moved to sa on every run.

Proposal

Add a switch (working name -UpdateModified, from #10726) that updates an existing SQL login in place when it differs from the source:

• Password: compare the password hash (Get-LoginPasswordHash, the value the copy already uses). When it differs, run ALTER LOGIN ... WITH PASSWORD = 0x... HASHED, the same statement Set-DbaLogin -PasswordHash runs. Nothing is dropped, so SID, ownerships, sessions and permissions stay as they are.
• Properties: bring the properties New-DbaLogin copies in line the same way: disabled state, default database, default language, CHECK_POLICY, CHECK_EXPIRATION. Set-DbaLogin covers all of these except the default language.
• Different SID: an in-place update can't fix that. Leave the login alone and report it with a warning, not just a verbose line: on an AG replica a different SID leaves the database users orphaned after a failover. -Force remains the way to replace such a login.
• Precedence: when both switches are given, the in-place update wins, as -UseJobLastModified does for jobs. Only a SID mismatch falls back to drop and recreate.
• Sync-DbaAvailabilityGroup: add a pass-through, like -UseJobLastModified in Sync-DbaAvailabilityGroup: add -UseJobLastModified pass-through to Copy-DbaAgentJob #10768.

Things that need no new code:

• Logins created separately on each replica have different hashes even with the same password, because the hash is salted. The first run updates them once. That is harmless: the password stays the same and nothing is dropped.
• Permissions and server roles are already synced on every run, by Update-SqlPermission in Copy-DbaLogin and by Sync-DbaLoginPermission in Sync-DbaAvailabilityGroup.
• Windows logins and groups have no password, so only the properties apply. Starting with SQL logins is fine.
• Without the new switch, nothing changes.

Is there a command that is similiar or close to what you are looking for?

Yes. Set-DbaLogin -PasswordHash already does the in-place password update for a single login.

Technical Details

• Skip without -Force: Copy-DbaLogin.ps1 line 328
• Ownership change and drop with -Force: lines 437-467
• Copy-DbaLogin already refuses to copy from 2012+ to an older version because the hash format changed. The same check covers the in-place update.

This text was created by Claude and reviewed by Andreas Jordan.