How do I prepare the MSSQL user for the JDBC 13.x driver?

Question

I have received an alert that I need to give additional permissions to the MSSQL user, what steps do I need to do to accomplish this?

Answer 1

Steps (Jump to step 3 if you already know which permission is needed):

 

Step 1 — Identify SQL Server version

Run in SSMS/Azure Data Studio:

SELECT @@VERSION AS SqlVersion;

If it says SQL Server 2022 (16.x) or newer, you’ll grant VIEW DATABASE PERFORMANCE STATE. If it’s 2019 or earlier, grant VIEW DATABASE STATE. See Microsoft’s DMV permission note. Microsoft Learn

 

Step 2 — Check whether the QIE user already has the permission

Change database context to QIE DB (e.g., qie) and run:

USE [qie];
SELECT
  DB_NAME() AS database_name,
  HAS_PERMS_BY_NAME(
        DB_NAME(),
        'DATABASE',
        'VIEW DATABASE PERFORMANCE STATE'
  ) AS has_view_db_perf_state,
  HAS_PERMS_BY_NAME(
        DB_NAME(),
        'DATABASE',
        'VIEW DATABASE STATE'
  )AS has_view_db_state;

A result of 1 means the permission is present. HAS_PERMS_BY_NAME reference. Microsoft Learn Note which one applies based on step 1.

 

Step 3 — Grant the minimum required permission

Replace <DbUserName> with the QIE user used by the QIE application. Default is "qie". 

If you are on SQL Server 2022+

USE [qie];
-- If the principal doesn't exist in this DB yet, create it (examples below).
GRANT VIEW DATABASE PERFORMANCE STATE TO [<DbUserName>];

If you are on SQL Server 2019 or earlier

USE [qie];
GRANT VIEW DATABASE STATE TO [<DbUserName>];

(Older versions use the classic VIEW DATABASE STATE for database-scoped DMVs.) Microsoft Learn

 

Summary

Before QIE can update to the latest Microsoft JDBC Driver this new permission must be granted to allow QIE to continue to correctly manage schema changes on upgrades. Starting with Microsoft JDBC Driver 13.x, the driver changed how it gathers index metadata. When QIE calls DatabaseMetaData.getIndexInfo to manage schema changes, the driver now queries DMVs instead of sp_statistics. On SQL Server 2022+, those DMV queries require the VIEW DATABASE PERFORMANCE STATE permission (on earlier versions it’s VIEW DATABASE STATE). If the QIE user lacks this permission, you’ll see errors like:

• o VIEW DATABASE PERFORMANCE STATE permission denied in database 'qie'

• o The user does not have permission to perform this action.

Grant the appropriate permission to the QIE database and the QIE user. Root cause details are in Microsoft’s release notes and DMV permission docs. Microsoft Learn+1

 

Affected

• o Driver versions: Microsoft JDBC 13.x (e.g., 13.2.x). Microsoft Learn
• o SQL Server versions:
  • - SQL Server 2022 (16.x) and later: requires VIEW DATABASE PERFORMANCE STATE
  • - SQL Server 2019 (15.x) and earlier: requires VIEW DATABASE STATE. Microsoft Learn

 

Symptoms you may see

• o QIE fails during startup / schema validation.
• o Messages similar to:
  • - SQL Error: 262, SQLState: S0001
  • - VIEW DATABASE PERFORMANCE STATE permission denied in database '<db>'
  • - The user does not have permission to perform this action.
• Stack traces showing SQLServerDatabaseMetaData.getIndexInfo(...).

 

Why this happens:

The JDBC 13.x driver replaced its older metadata path and now issues custom queries (covering all index types, including columnstore) instead of sp_statistics. Those queries hit DMVs that, on SQL Server 2022+, require the new PERFORMANCE STATE permission. Microsoft Learn

 

FAQ

Q: Which permission do I need?

• o SQL Server 2022+ → VIEW DATABASE PERFORMANCE STATE
• o SQL Server 2019 or earlier → VIEW DATABASE STATE Microsoft Learn

Q: Does this grant expose table data?

It grants visibility into performance-related DMVs for the database; it does not grant SELECT on user tables. See Microsoft’s DMV permission model for specifics. Microsoft Learn

Q: Our app uses a Managed Identity—what’s different?

Create the QIE database user from the external provider and grant the same permission in each DB:

USE [qie];
CREATE USER [MyManagedIdentityDisplayName] FROM EXTERNAL PROVIDER;
GRANT VIEW DATABASE PERFORMANCE STATE TO [MyManagedIdentityDisplayName];

Reference: CREATE USER ... FROM EXTERNAL PROVIDER. Microsoft Learn

 

References

• o Microsoft JDBC Driver release notes — index metadata change in 13.x. Microsoft Learn
• o DMV permission changes in SQL Server 2022+: VIEW DATABASE PERFORMANCE STATE. Microsoft Learn
• o HAS_PERMS_BY_NAME function (permission checks). Microsoft Learn
• o CREATE USER (SQL logins and Entra/Managed Identity contained users). Microsoft Learn

 

See also

    SQL Server script to create High Availability database for QIE