Remove-DbaDbOrphanUser not removing users even when using -Force

[danpousson]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No error

Steps to Reproduce

if I run this: Remove-DbaDbOrphanUser -SqlInstance $conn -Force -EnableException it runs with no error or message.

If I then run: Remove-DbaDbOrphanUser -SqlInstance $conn -Force -EnableException -Debug It finds users to remove and prompts to confirm the removal of users that did not even show up on the first run.

Why are these users not getting removed by passing -force? Why do these users only show up if I pass -debug? Why is it prompting when passing -force -debug

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

2.0.2

Other details or mentions

Attaching results for:

Get-DbaDbUser -SqlInstance $conn -Database DTToolbox | ? { ($_.name -like "*DBU*") -or ($_.name -like "*ugly*")}

Attaching results for:

Remove-DbaDbOrphanUser -SqlInstance $conn -Force -EnableException -Debug -WhatIf

Get-DbaDbUser.txt Remove-DbaDbOrphanUser_WhatIF.txt

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

7.3.2

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64)   Sep 12 2022 15:07:06   Copyright (C) 2019 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

.NET Framework Version

PSChildName                      Version
-----------                      -------
v2.0.50727                       2.0.50727.4927
v3.0                             3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation  3.0.6920.4902
v3.5                             3.5.30729.4926
Client                           4.8.03761
Full                             4.8.03761
Client

[danpousson]

I attached results for these 2 commands: Remove-DbaDbOrphanUser not removing users even when using -Force

Remove-DbaDbOrphanUser -SqlInstance $conn -Force -EnableException -Debug -WhatIf;

[danpousson]

I am also getting this error frequently, but not if i pass -debug. I get the error even if I kill the sql spid first. Sort of 2 different issues but all of the same details

Remove-DbaDbOrphanUser -SqlInstance $conn -Force -EnableException Exception: Line | 151349 | throw $records[0] | ~~~~~ | The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

[danpousson]

I also keep getting this error with this same cmdlet: There is already an open DataReader associated with this Connection which must be closed first.

[andreasjordan]

Be aware that the command only outputs changes or dropping of schemas. There is not output fo the dropped users. But you can check with Get-DbaDbOrphanUser if there are still orphan users.

I can not reproduce the issue. In all of my tests, the users are dropped. Please create a script that creates a new database, a new login, a new user and maybe other objects that are needed and then remove the login to have an orphan user that you would like to remove. Then I can rebuild the same situation on my side and reproduce the issue.

[danpousson]

thank you I will give this a try

[andreasjordan]

I close this now, but can reopen if needed.