dataplat
Get-DbaDbOrphanUser contained availability group
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
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.
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.
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.