"Copy-DbaLogin -Force" removes [BUILTIN\Administrators] on SQL Server on Linux

[kekcjkee]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No explicit error message was received, but the use of Copy-DbaLogin -Force results in the removal of the BUILTIN\Administrators login on SQL Server running on Linux. This leads to operational issues because BUILTIN\Administrators is required for certain administrative tasks.

Steps to Reproduce

# Run the following command on SQL Server on Linux
Copy-DbaLogin -Source <SourceInstance> -Destination <DestinationInstance> -Force

• The command removes the BUILTIN\Administrators login, which is required for some administrative tasks on SQL Server.
• In SQL Server on Linux, restoring this account requires a rebuild of system databases, which is a complex process.

Please confirm that you are running the most recent version of dbatools

2.1.26

Other details or mentions

This behavior is documented in Microsoft’s documentation, SQL Server on Linux FAQ, under point 11. However, it is inconvenient for users. A potential solution could be to add a check when running Copy-DbaLogin on SQL Server on Linux, either warning the user or requiring confirmation before removing BUILTIN\Administrators.

Yes, I am aware that using -ExcludeLogin BUILTIN\Administrators can prevent this issue - I do use this option, but memory isn’t perfect, and it’s easy to forget to include it each time.

I understand that this may be considered a user-specific issue, and I am open to the possibility that this may not warrant any changes in the module itself. However, I wanted to bring this to your attention in case there is interest in adding this safeguard for other users who might encounter the same issue.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

PSVersion 5.1.19041.4291
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.4291
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU15-GDR) (KB5046059) - 16.0.4150.1 (X64) Sep 25 2024 17:34:41 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS)

.NET Framework Version

.NET Framework 4.8.4712.0

[mattcargile]

So I am confused. Are you copying from a Linux instance to another Linux instance? Does the login get removed from the destination or the source instance?

[kekcjkee]

So I am confused. Are you copying from a Linux instance to another Linux instance? Does the login get removed from the destination or the source instance?

It gets deleted on the destination server and cannot be created afterward.
Yes, I copied logins from one SQL Server on Linux to another identical one.

[mattcargile]

Weird. I guess there needs to be a custom special check similar to the below.

dbatools/public/Copy-DbaLogin.ps1

Lines 218 to 226 in 55f5166

	if ($Login.LoginType -like 'Window*' -and $destServer.DatabaseEngineEdition -eq 'SqlManagedInstance' ) {
	if ($Pscmdlet.ShouldProcess($destinstance, "$Login is a Windows login and not supported on a SQL Managed Instance, skipping on $destInstance")) {
	Write-Message -Level Verbose -Message "$Login is a Windows login and not supported on a SQL Managed Instance, skipping on $destInstance"
	$copyLoginStatus.Status = "Skipped"
	$copyLoginStatus.Notes = "$($Login.name) is a Windows login, not supported on a SQL Managed Instance"
	$copyLoginStatus | Select-DefaultView -Property DateTime, SourceServer, DestinationServer, Name, Type, Status, Notes -TypeName MigrationObject
	}
	continue
	}

Additionally, maybe New-DbaLogin needs to be looked at as to why it isn't throwing correctly, assuming mssql on Linux actually throws from T-SQL.

dbatools/public/Copy-DbaLogin.ps1

Lines 350 to 370 in 55f5166

	try {
	$splatNewLogin = @{
	SqlInstance = $destServer
	InputObject = $Login
	NewSid = $NewSid
	LoginRenameHashtable = $LoginRenameHashtable
	}
	if ($Login.DefaultDatabase -notin $destServer.Databases.Name) {
	$copyLoginStatus.Notes = "Database $($Login.DefaultDatabase) does not exist on $destServer, switching DefaultDatabase to 'master' for $($Login.Name)"
	Write-Message -Level Warning -Message $copyLoginStatus.Notes
	$splatNewLogin.DefaultDatabase = 'master'
	}
	$destLogin = New-DbaLogin @splatNewLogin -EnableException:$true
	$copyLoginStatus.Status = "Successful"
	} catch {
	$copyLoginStatus.Status = "Failed"
	$copyLoginStatus.Notes = (Get-ErrorMessage -Record $_)
	$copyLoginStatus | Select-DefaultView -Property DateTime, SourceServer, DestinationServer, Name, Type, Status, Notes -TypeName MigrationObject
	Write-Message -Level Verbose -Message "Could not create $newuserName on $destinstance | $PSItem"
	continue
	}