Install-sqmDiscoveryRole
Inventory
sqmSQLTool v1.9.231+ · Install
✓ -WhatIf supported
@@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
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | One or more instances. Pipeline-capable, a server list can be piped in. |
| -LoginName | string | Optional | , | 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. |
| -RoleName | string | Optional | SQL_ServiceNow_Discovery | Name of the server role. Square brackets are not accepted. |
| -SqlCredential | PSCredential | Optional | , | The DBA's own connection. |
| -ContinueOnError | switch | Switch | $false | Continue with the next instance on error. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately. |
| -WhatIf / -Confirm | switch | Optional | , | Shows what would be created and granted, changes nothing. |
Execution Flow
Return value
One object per instance with SqlInstance, RoleName, RoleCreated, Granted, LoginName, LoginCreated, MemberAdded, Status and Message.
| Status | Meaning |
|---|---|
OK | The role exists with the rights; the login is a member if one was given. |
Error | Setup 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 FullOnly create the role, add the accounts later
Install-sqmDiscoveryRole -SqlInstance SQL01, SQL02