Set-DbaLogin self-password reset issue

[MarcRPro]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [16:52:41][Get-DbaServerRoleMember] Failure | Login failed for user 'MYUSER'.

Steps to Reproduce

$sqlcred = New-Object System.Management.Automation.PSCredential ("MYUSER", $sqlsecurepass)
$newsecurepass = ConvertTo-SecureString -String $newpass -AsPlainText -Force
$server=Connect-DbaInstance -SqlInstance $sqlinstance -sqlcredential $sqlcred -TrustServerCertificate
$r=Set-DbaLogin -SqlInstance $sqlinstance -sqlcredential $sqlcred -login "MYUSER" -SecurePassword $newsecurepass

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

2.1.23

Other details or mentions

Hello,

When I try to self reset my password with Set-DbaLogin, I receive this warning.

WARNING: [16:52:41][Get-DbaServerRoleMember] Failure | Login failed for user 'MYUSER'.

By self reset my password, I mean I use a "MYUSER" login with Connect-DbaInstance and just after I change the password of "MYUSER" login with function Set-DbaLogin

It works properly but it also generates some SQL error logs

Login failed for user MYUSER . Reason : Password did not match that for the login provided (CLIENT 10.xx.xx.xx)
Error : 18456, severity: 14, State: 8
The client was unable to reuse a session with SPID 77, which had been reset for connection pooling,....
Error : 18056, severity: 20, state:8
Login failed for user MYUSER . Reason : Password did not match that for the login provided (CLIENT 10.xx.xx.xx)
Error : 18456, severity: 14, State: 8

(and I trigger some mail alerts when this kind of message happens in SQL)

It happens because in source code of Set-DbaLogin there's a Get-DbaServerRoleMember just after the Alter()

                # Alter the login to make the changes
                $l.Alter()

                # Retrieve the server roles for the login
                $roles = Get-DbaServerRoleMember -SqlInstance $server | Where-Object { $_.Name -eq $l.Name }

Is there a workaround ?

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

PSVersion 5.1.17763.6292
PSEdition Desktop

SQL Server Edition and Build number

SQL 2019 build 14.0.3475.1

.NET Framework Version

.NET Framework 4.8.4739.0

[niphlod]

I don't think Set-DbaLogin was thought to work for such scenarios.
Setting the password of the user holding the connection is also "risky business" : what happens if you loose connection in-between and that user is the only one you have ?
Usually users are set by a "login manager/security manager" login which has permissions to manage logins and permissions at server-level .

[MarcRPro]

You're totally right. but it's a special case : I'm writing a script for a "Privilege Access Management" system (like CyberArk, Delinea,...). Once a password has been used one time, the PAM system require a self password change.
Of course there are multiple sysadmin users, if self-password change failed

For the moment I simply stopped using Set-DbaLogin and used Invoke-DbaQuery with an "ALTER LOGIN" query