· Uwe Janke
Commands / Test-sqmLoginGroupAccess

Test-sqmLoginGroupAccess

SecuritysqmSQLTool v1.9.119+ · Test🔓 VIEW ANY DEFINITION + AD read access
In environments where SQL Server logins are created only as AD groups rather than per user, a user who cannot connect is missing one of two things: either none of their AD groups is provisioned as a SQL Server login at all (or the login exists but is disabled, or explicitly denied CONNECT SQL), or a usable login does exist for one of their groups but has no matching database user in the target database, because no CREATE USER ... FOR LOGIN was ever run there. This function answers both questions in one pass: it resolves every AD group the user belongs to, direct and nested, via Get-sqmADMemberGroups; checks sys.server_principals on -SqlInstance for a Windows-group login matching each of those groups and whether it is enabled and not denied; and for every login found this way checks each accessible database's sys.database_principals by SID, not by name (names can differ) for a mapped user and its role memberships. Results are saved as TXT and HTML report in -OutputPath, and the flat array of per-user result objects, each with a nested per-group Detail[], is still returned. A reachable domain controller is required, so this is not usable in a workgroup-only lab.

Parameters

ParameterTypeRequiredDefaultNotes
-Identitystring[]Required, The AD user having trouble logging in. SamAccountName, UPN or DistinguishedName. Pipeline-capable.
-SqlInstancestringOptional$env:COMPUTERNAMEInstance to check logins and database access on.
-SqlCredentialPSCredentialOptional, SQL or Windows credential. Without it: Windows authentication.
-DomainstringOptionalauto-detectedAD domain. Auto-detected when not specified (see Get-sqmADMemberGroups).
-Depthint (0-10)Optional2Maximum AD group nesting depth to resolve, passed through to Get-sqmADMemberGroups.
-Databasestring[]Optionalall accessible databasesRestrict the database-level check to these databases (wildcards allowed).
-ExcludeDatabasestring[]Optional@()Databases to skip during the database-level check (wildcards allowed).
-ExcludeSystemDatabasesswitchSwitch$falseSkip master, model, msdb and tempdb during the database-level check.
-OutputPathstringOptionalGet-sqmDefaultOutputPath\LoginGroupAccessOutput directory for the TXT/HTML report files.
-NoOpenswitchSwitch$falseDo not automatically open the generated report.
-ContinueOnErrorswitchSwitch$falseContinue with the next identity on error instead of throwing.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately instead of returning as errors.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES Get-sqmADMemberGroups -Identity ... -Depth N Resolves all AD groups of the user, direct and nested Needs a reachable domain controller - not usable in a workgroup lab Any group provisioned as a SQL Server login? NO Cause 1: no login at all None of the user's groups exists as a Windows-group login on the instance YES Login enabled and not denied CONNECT SQL? NO Login exists but is unusable is_disabled = 1, or DENY CONNECT SQL YES Per database: match sys.database_principals by SID Matched by SID, never by name - user and login names can differ -Database / -ExcludeDatabase / -ExcludeSystemDatabases narrow the scan Mapped database user found? NO Cause 2: no database user CREATE USER ... FOR LOGIN was never run in that database YES Collect database role memberships Shows what the user actually gets once connected Write TXT + HTML report -OutputPath, default <config>\LoginGroupAccess · opened unless -NoOpen Return one [PSCustomObject] per identity Identity · SqlInstance · GroupCount · LoginFound · Status Detail[] - one nested row per AD group with login and database mapping DONE

Examples

Why can this user not log in?
Test-sqmLoginGroupAccess -Identity "john.doe" -SqlInstance "SQL01"
Narrow the check to one database
Test-sqmLoginGroupAccess -Identity "john.doe" -SqlInstance "SQL01" -Database "AdventureWorks"
Several users at once, user databases only
"john.doe","jane.roe" | Test-sqmLoginGroupAccess -SqlInstance "SQL01" -ExcludeSystemDatabases -ContinueOnError
Resolve deeper group nesting
Test-sqmLoginGroupAccess -Identity "john.doe" -SqlInstance "SQL01" -Depth 5
← Back
Command Index
Related
Get-sqmADMemberGroups
Related
Get-sqmLoginPermissions