Get-DbaDbOrphanUser Improvement #7253

Activity

1. devendra2108

   added

   feature

   on Apr 22, 2021
2. andreasjordan

   added

   maintainer approval - pendingMaintainer must approve this before moving forwardMaintainer must approve this before moving forward

   on Mar 16, 2026
3. potatoqualitee commented on Jul 14, 2026

   

   potatoqualitee

   on Jul 14, 2026 – with ChatGPT Codex Connector

   Member

   More actions

   I researched the proposed Windows-group check. It can provide useful effective access information, but using it to suppress an orphan result would blur two different concepts and can make cleanup less safe.

   The semantic problem

   A database principal is orphaned when its SID no longer maps to the intended server principal. A Windows user might still reach the database through a separate Windows-group login, but that does not repair the explicit database user's SID mapping. Treating “has access through some group” as “not orphaned” could hide stale principals and change what Remove-DbaDbOrphanUser sees.

   The safer model is:

   • keep IsOrphaned based on principal/SID mapping;
   • optionally enrich Windows-user results with effective group access information;
   • let callers decide whether group-derived access changes their cleanup policy.

   Resolution options and drawbacks

   1. xp_logininfo '<domain\user>', 'all' is the closest server-side mechanism. Microsoft documents several constraints: it requires CONTROL SERVER or explicit execute permission, SQL Server must contact a domain controller, an unreachable DC returns no information, and it only reports Active Directory global groups, not universal groups: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql?view=sql-server-ver17

      Running this once per candidate user is also an N+1 network/domain lookup. Large estates, nested groups, stale accounts, cross-forest trusts, local groups, or SQL-authenticated execution can make it slow or incomplete. “No row” cannot safely mean “no group access”; it might mean resolution was unavailable.

   2. sys.login_token only describes the token of the current connection. It cannot answer membership for arbitrary orphan candidates.

   3. Client-side Active Directory lookup would add an RSAT/ActiveDirectory-module dependency, differ across Windows PowerShell and PowerShell Core/Linux, require separate directory credentials/permissions, and still face nested-group and trust-boundary ambiguity. That is not a good default dependency for this command.

   4. Enumerating every server Windows-group login and every member reverses the lookup but is even more expensive, can expose large directory memberships, and inherits the same xp_logininfo limitations.

   Recommended design

   If this proceeds, make it opt-in (for example, -ResolveWindowsGroupAccess) and return additional properties such as:

   • HasAccessViaWindowsGroup: $true, $false, or $null when resolution failed/was unavailable
   • WindowsGroupPermissionPath: matching server group login(s)
   • WindowsGroupResolutionError: concise reason when indeterminate

   Do not remove the principal from orphan results by default. A second explicit filter switch could be considered later, but it should never treat indeterminate resolution as safe.

   Tests can mock xp_logininfo for direct, nested/multiple, empty, and error paths. A live integration test would require a controlled AD domain and should not be the only validation.

   The main drawback is therefore not implementation difficulty; it is that group-derived effective access is not the same as a valid database-to-login mapping, and conflating them could make automated orphan cleanup dangerous.
4. SQLCanuck commented on Oct 27, 2026

   

   SQLCanuck

   on Oct 27, 2026

   More actions

   For both Get-DbaDbOrphanUser and Remove-DbaDbOrphanUser, it would also be nice to have the ability to optionally exclude specific logins.