Remove-SqlOrphanUser fails

Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'dbname'. "
At C:\Users\user\Documents\WindowsPowerShell\Modules\dbatools\functions\Remove-SqlOrphanUser.ps1:219 char:37

•                                 $server.Databases[$db.Name].ExecuteNonQuery( ...
  

•                                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : FailedOperationException

[wsmelton]

Would you be able to provide following information:

• Exception log (C:\Users\<username>\Documents\dbatools-exceptions.txt)
• SQL Server version

I cannot find the exceptions file in that path. Is there anything that I need to do, to log the output?
SQL Server version is SQL 2012 SP3+CU3

[chainshaw]

I'm also encountering this error on several different versions of SQL Server. I believe this happens when a user owns a schema. I wasn't able to get output from the dbatools-exceptions.txt file, but I grabbed the full error from Powershell.

remove-sqlorphanuser exception.txt

[ClaudioESSilva]

Regarding this issue what you expect to happen?

1. drop schema (if same name and if not owner on others schemas) and then drop the user
2. verify if user is owner on some schema and if true just send warning message saying "he is owner, you have to change the owner of schema 1, 2 and X first"
3. Change the owner to dbo and then drop user (this will leave "orphan schemas")
4. A mix! Do the 1st option if owner in only one and the name is equal. do the 2nd option if owns more than one
5. If you have any other suggestion... please share 😄

[chainshaw]

What about defaulting to #2 but doing #1 with some sort of Force switch? That way you can run it once to see, then run again with the switch to do the drop. It'd also give you a level of acknowledgement that the user is okay with dropping any objects in those schemas.

[ClaudioESSilva]

I was thinking on the 4th option. Just because a schema can only have one owner, so if this is true, we have the job done. no magic here, no extra steps for the user (of course only if no objects exists for that schema).
If this is not true, any action we take may not be the correct one in the user's perspective.

@Neghtasro your suggestion would be do number 2. And then
If -Force was specified will do the number 3 before number 1? or do the number one directly? (once again after we verify that are no objects under the schema)

[ClaudioESSilva]

@arvindravish @Neghtasro I have done some improvements to address this issue.
Feel free do download the branch #415, test it and give feedback if you don't mind 😄
Thanks to @ConstantineK for the review.

[potatoqualitee]

fixed in #415! 🥇