SQL Server user permissions needed for OpenTelemetry collector sqlserverreceiver to get default metrics

[divyankm]

I have an otel collector v. 0.13 and file like this:

  receivers:
    sqlserver:
      collection_interval: 30s
      server: "your-sqlserver-host"       # Use 'server' instead of 'endpoint'
      port: 1433                          # Port is separate
      username: "${SQLSERVER_USER}"
      password: "${SQLSERVER_PASSWORD}"
      # enable_query_sampling: true         # Optional, defaults to false
      # max_rows_per_query: 50              # Optional, default is 100
    # sqlserver/1: # If multiple MSSQL Servers in Single Windows Instance
    hostmetrics:
      collection_interval: 30s
      scrapers:
        cpu:
        memory:
    # windowseventlog: #  Uncomment Only If > Configure if Windows Logs are required
    #       channel: application,system,security
exporters:
  otlphttp/mimir-ms: #mimir-microservice
    endpoint: < >
    headers:
      "X-Scope-OrgID": "primary"
    tls:
      insecure: true

service:
  pipelines:
    metrics:
      receivers: [ sqlserver,hostmetrics ]
      exporters: [ otlphttp/mimir-ms ]
    # logs: #  Uncomment Only If > Configure if Windows Logs are required
    #   receivers: [ windowseventlog ]
    #   exporters: [ otlphttp/loki ]

I have created SQL Server user otel_collector_readonly and granted these permissions:

-- Let the user connect to SQL Server
GRANT CONNECT SQL TO <username>;
-- Let the user see all databases
GRANT VIEW ANY DATABASE TO <username>;
GRANT VIEW ANY DEFINITION TO <username>;
GRANT VIEW ANY PERFORMANCE TO <username>;
-- Required: Allow access to system-level DMVs and performance counters
GRANT VIEW SERVER STATE TO <username>;

Currently I'm seeing only one metric sqlserver.lock.wait.rate appearing in SQL Server.

Which permissions are required to get all metrics as per link: https://github.com/open-telemetry/opentelemetry-collector-contrib/blob/main/receiver/sqlserverreceiver/documentation.md#default-metrics

While I add non default metrics in otel file as enabled, these metrics coming in prometheus.

    metrics:
      sqlserver.database.count:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true

but default metrics not coming in prometheus.