· Uwe Janke
Commands / Test-sqmDiscoveryAccess

Test-sqmDiscoveryAccess

Inventory sqmSQLTool v1.9.135+ · Test ✓ -WhatIf supported
Verifies that an inventory or discovery service account can actually read what it is supposed to read, across a list of instances. Tools like ServiceNow Discovery, SCOM or an authenticated vulnerability scanner connect with a dedicated account. When that account is missing on an instance, the collection fails silently from the DBA's point of view: the CMDB entry goes stale and, in ServiceNow, a CI marked "absent" does not return to active on its own.

What is reported per instance: does the login exist, is it enabled, is CONNECT SQL denied; is it a member of the expected server role; which effective server permissions it really has (direct grants, every server role it is a member of, nested, and public, with DENY winning); whether VIEW ANY DATABASE is still granted to public; whether the account is over-privileged (sysadmin is never required for discovery).

DiscoveryLevel is what the collecting tool can actually see: None or ServerOnly (instance data only, which needs no SQL login), Databases (names, state, recovery model, collation, owner), DatabaseDetails (additionally sizes and file paths, needs VIEW ANY DEFINITION) and Full (additionally port, CPU/RAM, cluster and AG topology, needs VIEW SERVER STATE).

The hardening trap. Without VIEW ANY DATABASE SQL Server reports no error: the account simply sees only master and tempdb. That happens when a hardening baseline revoked the permission from public. The function reads read-only and changes nothing. If the account gets access through an AD group login, it is reported as missing; use Test-sqmLoginGroupAccess for that.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestring[]Optional$env:COMPUTERNAMEOne or more instances. Pipeline-capable, a server list can be piped in.
-LoginNamestringRequired, The service account to verify, for example CORP\SVC-SNOW-DISCO. Windows login, Windows group login or SQL login.
-ServerRolestringOptional, Expected server role membership. Without it the role check is skipped.
-RequiredLevelstringOptionalDatabasesServerOnly, Databases, DatabaseDetails or Full. Anything below it fails the instance.
-SqlCredentialPSCredentialOptional, The DBA's own connection, not the account being checked.
-OutputPathstringOptional<OutputPath config>\DiscoveryAccessReport directory.
-NoOpenswitchSwitch$falseDo not open the report.
-ContinueOnErrorswitchSwitch$falseContinue with the next instance on error.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately.
-WhatIf / -ConfirmswitchOptional, Skips the check and marks the instances WhatIf.

Execution Flow

START One read-only query per instance login, server roles (nested), effective permissions with DENY winning, public Login exists, enabled, CONNECT SQL ok? NO Level ServerOnly nothing is collected from SQL level on YES VIEW ANY DATABASE effective? NO Level ServerOnly sees only master and tempdb YES Derive the DiscoveryLevel Databases; + VIEW ANY DEFINITION = DatabaseDetails; + VIEW SERVER STATE = Full Findings missing role, sysadmin (over-privileged), public lost VIEW ANY DATABASE missing VIEW ANY DEFINITION or VIEW SERVER STATE, each with its consequence Status and report below -RequiredLevel: Fehler; findings only: Warnung; otherwise OK TXT and HTML report in -OutputPath, objects returned END

Return value

One object per instance with Reachable, LoginExists, IsDisabled, DenyConnectSql, IsSysadmin, ServerRoles, InServerRole, PublicViewAnyDb, ViewAnyDatabase, ViewAnyDefinition, ViewServerState, GrantedPermissions, DiscoveryLevel, RequiredLevel, MeetsRequirement, Findings and Status.

StatusMeaning
OKThe required level is reached and there are no findings.
WarnungThe required level is reached, but there are findings (for example sysadmin, a missing optional permission).
FehlerThe account stays below the required level.
ErrorThe instance could not be checked (connection or query failed).
WhatIf-WhatIf: nothing was checked.

The status words Warnung and Fehler are the literal values the function returns. On SQL Server 2022 and later the server reports VIEW SERVER PERFORMANCE STATE instead of VIEW SERVER STATE.

Examples

One instance against the default requirement (server plus database listing)
Test-sqmDiscoveryAccess -SqlInstance SQL01 -LoginName "CORP\SVC-SNOW-DISCO"
Sweep a server list, also checking the role
Get-Content .\sqlserver.txt | Test-sqmDiscoveryAccess -LoginName "CORP\SVC-SNOW-DISCO" `
    -ServerRole "SQL_Inventory_RO_Basic" -NoOpen
Require the DMVs as well (port, CPU/RAM, cluster and AG topology)
Test-sqmDiscoveryAccess -SqlInstance SQL01, SQL02 -LoginName "CORP\SVC-SCOM" -RequiredLevel Full
Only the instances that need attention
Test-sqmDiscoveryAccess -SqlInstance SQL01, SQL02 -LoginName "CORP\SVC-SNOW-DISCO" -NoOpen |
    Where-Object Status -ne "OK" | Format-List SqlInstance, DiscoveryLevel, Findings
← Back
Command Index
Related
Test-sqmLoginGroupAccess
Related
Get-sqmLoginSettings