Set-SqlDscAudit: Cannot modify AuditGuid property - requires drop and recreate pattern

[coderabbitai]

Description

The Set-SqlDscAudit command fails when attempting to modify the AuditGuid property of an existing SQL Server audit object due to SQL Server restrictions.

Error Details

When running the integration test for modifying AuditGuid property, the following error occurs:

SmoException: Modifying the Guid property of the Audit object is not allowed. You must drop and recreate the object with the desired property.
FailedOperationException: Alter failed for Audit 'SqlDscTestSetAudit_983291254'. 
MethodInvocationException: Exception calling "Alter" with "0" argument(s): "Alter failed for Audit 'SqlDscTestSetAudit_983291254'. "

Steps to Reproduce

1. Create an audit using New-SqlDscAudit
2. Attempt to modify the AuditGuid property using Set-SqlDscAudit
3. The command fails with the above error

Expected Behavior

The command should successfully modify the AuditGuid property, either by:

1. Adding an opt-in parameter (e.g., -AllowRecreate) to Set-SqlDscAudit that enables dropping and recreating the audit object with the desired GUID. This should probably be in a parameter set together with parameter AuditGuid. Careful consideration need to be taken when setting other values of the passed parameters to the properties of the new audit object as the re-created object must maintain the exact values of the previous object, plus setting (overwriting) any new values passed in together with the AuditGuid parameter.
2. Creating a new command (e.g., Reset-SqlDscAudit) that supports the recreation pattern for properties that cannot be modified directly

Suggested Solutions

Option 1: Enhance Set-SqlDscAudit

Add an optional parameter like -AllowRecreate that when specified, allows the command to drop and recreate the audit object when encountering properties that cannot be modified directly.

Option 2: New Command

Create a new command Reset-SqlDscAudit that specifically handles the drop-and-recreate pattern for audit objects when direct modification is not supported.

Environment

• Command: Set-SqlDscAudit
• SQL Server: Multiple versions (2017, 2019, 2022)
• Module: SqlServerDsc

References

• PR: Add integration test for Set-SqlDscAudit command #2267
• Comment: Add integration test for Set-SqlDscAudit command #2267 (comment)

[johlju]

Depending on option used this section should be uncommented and updated to reflect option 1, or emoved if using option 2:

SqlServerDsc/tests/Integration/Commands/Set-SqlDscAudit.Integration.Tests.ps1

Lines 116 to 131 in f316f18

	# It 'Should modify audit AuditGuid property successfully' {
	# # Verify audit exists before modification
	# $originalAudit = Get-SqlDscAudit -ServerObject $script:serverObject -Name $script:testAuditName -ErrorAction Stop
	# $originalAudit | Should -Not -BeNullOrEmpty
	# $originalGuid = $originalAudit.Guid
	# $newGuid = [System.Guid]::NewGuid().ToString()
	# # Modify the audit
	# $null = Set-SqlDscAudit -ServerObject $script:serverObject -Name $script:testAuditName -AuditGuid $newGuid -Force -ErrorAction Stop
	# # Verify audit was modified
	# $modifiedAudit = Get-SqlDscAudit -ServerObject $script:serverObject -Name $script:testAuditName -ErrorAction Stop
	# $modifiedAudit.Guid | Should -Be $newGuid
	# $modifiedAudit.Guid | Should -Not -Be $originalGuid
	# }