· Uwe Janke
Commands / Install-sqmDiscoveryRole

Install-sqmDiscoveryRole

Inventory sqmSQLTool v1.9.231+ · Install ✓ -WhatIf supported
Creates a user-defined server role with exactly the read-only rights an inventory scan needs, and optionally adds the service account to it. The ServiceNow pattern "MSSql DB On Windows" reads @@version, sys.configurations, sys.dm_exec_sessions, sys.dm_os_schedulers and SERVERPROPERTY, and for Always On sys.dm_hadr_availability_group_states, sys.availability_groups, sys.availability_group_listeners and sys.availability_group_listener_ip_addresses.

What the role gets: VIEW ANY DEFINITION (catalog views; without it sys.master_files and the AG catalog views return zero rows instead of an error, so the scan reports success with empty sizes), VIEW ANY DATABASE (granted explicitly so a hardening that revokes it from public does not break the scan) and VIEW SERVER STATE for the DMVs (from SQL Server 2022 the finer VIEW SERVER PERFORMANCE STATE). Nothing else: no sysadmin, no db_owner, no db_datareader, no write access, no access to user data.

Idempotent. Running it again changes nothing that is in place; the GRANTs also restore a permission that was revoked later. Test-sqmDiscoveryAccess checks against the same definition, so setup and check cannot drift apart. The Windows side (WMI and DCOM) is set by Set-sqmDiscoveryWmiAccess. One role per service account is recommended, not one shared role for several scanners.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestring[]Optional$env:COMPUTERNAMEOne or more instances. Pipeline-capable, a server list can be piped in.
-LoginNamestringOptional, The service account, for example CORP\svc_servicenow. A missing Windows login is created, a missing SQL login is not (no password is invented). Without it only the role is created.
-RoleNamestringOptionalSQL_ServiceNow_DiscoveryName of the server role. Square brackets are not accepted.
-SqlCredentialPSCredentialOptional, The DBA's own connection.
-ContinueOnErrorswitchSwitch$falseContinue with the next instance on error.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately.
-WhatIf / -ConfirmswitchOptional, Shows what would be created and granted, changes nothing.

Execution Flow

START Read the SQL Server major version 2012 or newer is required; from 2022 the state right is VIEW SERVER PERFORMANCE STATE -WhatIf: report and stop here nothing is created, granted or added Read the current state does the role exist, does the login exist, is the login already a member CREATE SERVER ROLE (only if missing) and GRANT the three rights the GRANTs always run, so a right that was revoked later comes back With -LoginName: login and membership Windows account missing: CREATE LOGIN FROM WINDOWS; SQL login missing: stop with an error ALTER SERVER ROLE ... ADD MEMBER (only if not yet a member) Result object per instance RoleCreated, LoginCreated, MemberAdded, Granted, Status, Message END

Return value

One object per instance with SqlInstance, RoleName, RoleCreated, Granted, LoginName, LoginCreated, MemberAdded, Status and Message.

StatusMeaning
OKThe role exists with the rights; the login is a member if one was given.
ErrorSetup failed on this instance; Message has the reason.
WhatIf-WhatIf: nothing was changed.

Examples

Dry run: what would be created and granted
Install-sqmDiscoveryRole -SqlInstance SQL01 -LoginName "CORP\svc_servicenow" -WhatIf
Roll the role out over a server list, then verify it
Get-Content .\sqlserver.txt | Install-sqmDiscoveryRole -LoginName "CORP\svc_servicenow"
Test-sqmDiscoveryAccess -SqlInstance (Get-Content .\sqlserver.txt) -LoginName "CORP\svc_servicenow" `
    -ServerRole SQL_ServiceNow_Discovery -RequiredLevel Full
Only create the role, add the accounts later
Install-sqmDiscoveryRole -SqlInstance SQL01, SQL02