Get-DbaDbOrphanUser contained availability group

[Gincules]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Server principals ##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin## in contained always on group databases master and msdb returned as orphan user.





With contained AGs, each AG has its own independent set of system databases (master, msdb) that live inside the availability group. Each contained system database (master, msdb) inside the AG creates its own instance of these special logins with different sids. This means dbatools result is incorrect.

Steps to Reproduce

Get-DbaDbOrphanUser -SqlInstance localhost -Database contained_ag_db_master
Get-DbaDbOrphanUser -SqlInstance localhost -Database contained_ag_db_msdb

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

Major Minor Build Revision

---

2 5 5 -1

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.20348.3932

PSEdition Desktop

PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}

BuildVersion 10.0.20348.3932

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-CU20-GDR) (KB 5063814) - 16.0.4210.1 (X64)
Microsoft SQL Server 2025 (CTP2.1) - 17.0.800.3 (X64)

.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.04161

Full 4.8.04161

Client 4.0.0.0

[dbaduck]

This is actually normal for a contained AG. Because these special msdb and master databases are not in place as real master and msdb in the Contained AG, they report as orphaned, because they are certificate logins.

When you connect to the listener of the Contained AG, you no longer get orphaned users for these 2 databases when they are actually running as master and msdb. This shouldn't be an issue.

[Gincules]

In a perfect world I would agree and if you had a cluster with contained AVGs ONLY. But reality is driven by application compatibility and licensing costs.

We‘ve been trying to use contained databases since 2012 but some applications still don‘t support them which ends in mixed environments.

Same for AVGs as soon as you have a contained and a non-contained AVG on a cluster and connect to the non-contained it will return that as orphan. If I pipe it on Remove-DbaDbOrphanUser then goodbye. For now I exclude the user myself from the results but to me it's an issue.

[dbaduck]

I don't know if the responsibility is on dbatools to detect everything, but I am not a maintainer, so I cannot speak to standards they uphold. But if there is a contained AG, I would say it would be on the person using the function to exclude the databases that participate in the AG (Contained) as they should know if they connect to the instance that they will see these databases and exclude them.

[andreasjordan]

Yes, we can not support every edge case and have to leave some extra work for the user.

So I will change this to a feature request.

@Gincules If you really want this, you would need to provide a pull request or at least some code we can work on.