SqlLogin: Add AllowRecreate parameter to enforce Sid on existing logins

Context

In PR #2453, the Sid parameter was added to the SqlLogin resource to allow setting the security identifier (SID) for newly created SQL logins. However, the SID validation logic for existing logins is being removed from Test-TargetResource to avoid infinite drift loops, since SQL Server does not support changing a login's SID after creation.

This issue tracks the work to re-add SID validation and implement login recreation functionality in a future PR.

References:

• PR: https://github.com/dsccommunity/SqlServerDsc/pull/2453
• Comment: https://github.com/dsccommunity/SqlServerDsc/pull/2453#discussion_r2805035033
• Original issue: #1470
• Requested by: @johlju

---

Requirements

1. Re-add SID validation to Test-TargetResource

• Check if the Sid parameter is bound
• Compare the current login's SID with the desired SID
• Return $false if they don't match (when AllowRecreate is $true)
• If AllowRecreate is not set and SIDs mismatch, emit a warning but don't fail the test

2. Add login recreation logic to Set-TargetResource

• When Sid is bound and the login exists with a different SID
• Check if AllowRecreate parameter is $true
• If yes, drop the existing login and recreate it with the correct SID
• If no, emit a warning/error explaining manual intervention is required

3. Add AllowRecreate opt-in parameter

• New boolean parameter on all three functions (Get/Set/Test-TargetResource)
• Defaults to $false for safety
• When $true, allows the resource to drop and recreate the login to enforce the SID
• Must be explicitly set by the user to prevent accidental login deletion

---

Implementation Plan

Phase 1: Add AllowRecreate parameter

1. Add AllowRecreate parameter to Set-TargetResource, Test-TargetResource, and Get-TargetResource (for consistency)
2. Add parameter to schema MOF file (DSC_SqlLogin.schema.mof)
3. Add localized strings for recreation messages
4. Update comment-based help with parameter description and warnings

Phase 2: Implement Set-TargetResource logic

1. In the Present branch where login already exists:
   • After checking login existence, add SID validation block
   • If Sid is bound and login SID doesn't match:
     • If AllowRecreate is $true:
       • Log warning about recreation
       • Use $PSCmdlet.ShouldProcess for confirmation
       • Drop the existing login using Remove-SQLServerLogin
       • Continue to creation logic (reuse existing creation code)
     • If AllowRecreate is $false:
       • Throw terminating error with clear message that SID cannot be changed without recreation
       • Instruct user to set AllowRecreate = $true if recreation is acceptable

Phase 3: Update Test-TargetResource logic

1. Re-add SID comparison logic (similar to what was removed in PR #2453)
2. If Sid is bound and mismatches:
   • If AllowRecreate is $true: return $false (not in desired state)
   • If AllowRecreate is $false: emit warning and return $true (avoid drift loop)

Phase 4: Testing and Documentation

1. Add unit tests for:
   • Login recreation when SID mismatches and AllowRecreate = $true
   • Error thrown when SID mismatches and AllowRecreate = $false
   • ShouldProcess confirmation for recreation
   • Test-TargetResource behavior with/without AllowRecreate
2. Add integration test for login recreation scenario
3. Update resource documentation (README.md)
4. Add example configuration demonstrating AllowRecreate usage
5. Update CHANGELOG.md

---

Security Considerations

• Recreating a login will drop all associated permissions, roles, and ownerships
• The AllowRecreate parameter must default to $false for safety
• Documentation should clearly warn users about the implications of login recreation
• Consider logging which permissions/roles are lost during recreation (if feasible)
• Ensure ShouldProcess is used with appropriate ConfirmImpact level (High)

---

Alternative Approaches Considered

1. No recreation support: Keep current limitation, document that SID can only be set on new logins

   • Simpler but less functional
   • Users would need to manually drop/recreate logins

2. Automatic recreation without opt-in: Always recreate when SID mismatches

   • Too dangerous, could accidentally delete production logins

3. Separate Force parameter: Use existing DSC patterns

   • Less explicit about the recreation action
   • AllowRecreate is more descriptive and intentional