Commands / Enable-sqmMonitoringAccess
Enable-sqmMonitoringAccess
Security sqmSQLTool v1.8.2+ · Setup ✓ -WhatIf supported  ⚠ ConfirmImpact: High
Configures a monitoring account on every SQL Server instance found on the target computer. The function auto-discovers instances via the Windows Registry key InstalledInstances and performs the full setup on each one:

Per instance: Create/ensure a server role with VIEW SERVER STATE, VIEW ANY DATABASE, VIEW ANY DEFINITION, ALTER TRACE, and CONNECT ANY DATABASE; create/ensure the Windows login and add it to that role; create/ensure a database role in master and msdb with the matching database user and granular GRANT permissions (system views, xp_loginconfig, msdb proxy stored procs).

If -PolicyName is provided (or configured via Get-sqmConfig), the policy is disabled before setup and re-enabled afterwards — even on error. A log file is written to -OutputPath on completion.

Parameters

ParameterTypeRequiredDefaultNotes
-ComputerNamestringOptional$env:COMPUTERNAMETarget computer. All SQL Server instances on this computer are configured.
-MonitoringUserstringOptional*Get-sqmConfig 'DefaultMonitoringUser'Windows login of the monitoring account (e.g. DOMAIN\SvcMonitoring). Required if not set in module config.
-ServerRoleNamestringOptionalMonitoringRoleName of the server-level role to create.
-DatabaseRoleNamestringOptionalMonitoringDbRoleName of the database role created in master and msdb.
-PolicyNamestringOptionalGet-sqmConfig 'DefaultPolicy'SQL Server Policy to disable before and re-enable after setup. Omit (or leave unconfigured) to skip policy management.
-OutputPathstringOptionalC:\System\WinSrvLog\MSSQLDirectory for the log file.
-SqlCredentialPSCredentialOptional—SQL credential for all instance connections.
-ContinueOnErrorswitchSwitch$falseLog the error and continue to the next instance instead of aborting.
-EnableExceptionswitchSwitch$falseThrow immediately on error.
-WhatIf / -ConfirmswitchOptional—ShouldProcess (ConfirmImpact: High) gates the per-instance setup. -WhatIf returns Status='WhatIf' per instance.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -ComputerName provided? NO → Default: $env:COMPUTERNAME Resolve MonitoringUser & PolicyName MonitoringUser: param → Get-sqmConfig 'DefaultMonitoringUser' | still empty → throw PolicyName: param → Get-sqmConfig 'DefaultPolicy' | null = no policy management ComputerName reachable (Ping)? NO return error object Status='Error' YES Registry: SQL instances found? NO return error object no SQL Server found YES foreach instance in $instances (MSSQLSERVER → ComputerName | else → ComputerName\Instance) ShouldProcess? NO → Status='WhatIf', continue to next instance Connect-DbaInstance (connection test) PolicyName set? → SET is_enabled=0 (disable policy) Create/ensure server role → GRANT VIEW SERVER STATE, VIEW ANY DATABASE, VIEW ANY DEFINITION, ALTER TRACE, CONNECT ANY DATABASE Create/ensure Windows login → ADD MEMBER to server role Create/ensure database role in master + msdb → CREATE USER + ADD MEMBER GRANT: sys views, xp_loginconfig (master) | sp_enum_login_for_proxy, sysproxies (msdb) PolicyName set? → SET is_enabled=1 (re-enable policy) — also on catch catch: log error | re-enable policy (try) | throw if -EnableException or !-ContinueOnError Write log file to OutputPath MonitoringAccess_<computer>_<date>.log Return $results [PSCustomObject[]] ComputerName, SqlInstance, Status, Message DONE

Examples

Set up monitoring account on the local computer
Enable-sqmMonitoringAccess -MonitoringUser "CORP\SvcMonitoring"
Set up on a remote server with custom role names and policy management
Enable-sqmMonitoringAccess `
    -ComputerName "SQL01" `
    -MonitoringUser "CORP\SvcMonitoring" `
    -ServerRoleName "MonRole" `
    -DatabaseRoleName "MonDbRole" `
    -PolicyName "Enforce Password Policy" `
    -ContinueOnError
← Back
Command Index
Related
Grant-sqmTemporarySysadmin