Partition Stats Permissions: A SQL Server 2025 CU9 Counterpoint

Partition stats permissions on SQL Server 2025 CU9 produced a result that differs from the expected pair. In this controlled test, the performance-state grant alone returned partition metadata. The same identity still could not select the table rows.

Read the expected partition stats permissions

On SQL Server 2022 and later, sys.dm_db_partition_stats is expected to need both VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION. The requirement for earlier versions differs. Keep that expected pair alongside the exact engine and operation you test.

I first saw this on SQL Server 2025 CU9, build 17.0.5005.3, and the same result came back on CU8, build 17.0.4085.5. It concerns one restricted loginless user and a known three-row demonstration table. It does not establish a replacement permission contract for other builds, identities or diagnostic operations.

Keep setup separate from the restricted caller

Setup creates a demo database, a table with three sample rows and a dedicated diagnostic role. A loginless user joins that role. A small procedure records the table’s actual object ID before impersonation. The test therefore avoids relying on a restricted OBJECT_ID lookup returning a usable value.

CREATE DATABASE PartitionPermissionDemo;
GO
USE PartitionPermissionDemo;
GO
CREATE TABLE dbo.PartitionDemo(Id int NOT NULL PRIMARY KEY, Value varchar(20) NOT NULL);
INSERT dbo.PartitionDemo VALUES (1,'a'),(2,'b'),(3,'c');
CREATE USER RestrictedDemo WITHOUT LOGIN;
CREATE ROLE DiagnosticsDemo;
ALTER ROLE DiagnosticsDemo ADD MEMBER RestrictedDemo;
GO
CREATE PROCEDURE dbo.PartitionPermissionTest @Phase nvarchar(60)
AS
BEGIN
  DECLARE @ObjectId int = OBJECT_ID(N'dbo.PartitionDemo');
  DECLARE @Perf int, @Security int, @DmvError int, @DmvMessage nvarchar(2048),
    @ObservedRows bigint, @PartitionRows int, @IndexId int, @PartitionNumber int, @UsedPages bigint,
    @TableError int, @Rows int;
  EXECUTE AS USER = N'RestrictedDemo';
  SET @Perf = HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', N'VIEW DATABASE PERFORMANCE STATE');
  SET @Security = HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', N'VIEW SECURITY DEFINITION');
  BEGIN TRY
    SELECT @PartitionRows = COUNT(*), @ObservedRows = MAX(row_count),
      @IndexId = MAX(index_id), @PartitionNumber = MAX(partition_number),
      @UsedPages = MAX(used_page_count)
    FROM sys.dm_db_partition_stats
    WHERE object_id = @ObjectId AND index_id = 1;
  END TRY
  BEGIN CATCH
    SET @DmvError = ERROR_NUMBER();
    SET @DmvMessage = ERROR_MESSAGE();
  END CATCH;
  BEGIN TRY
    EXEC sys.sp_executesql N'SELECT @Rows = COUNT(*) FROM dbo.PartitionDemo',
      N'@Rows int OUTPUT', @Rows OUTPUT;
  END TRY
  BEGIN CATCH
    SET @TableError = ERROR_NUMBER();
  END CATCH;
  REVERT;
  SELECT @Phase AS Phase, @Perf AS PerformanceState, @Security AS SecurityDefinition,
    @DmvError AS DmvError, @ObservedRows AS ObservedRows, @PartitionRows AS PartitionRows,
    @IndexId AS IndexId, @PartitionNumber AS PartitionNumber, @UsedPages AS UsedPageCount,
    @TableError AS DirectSelectError, @DmvMessage AS DmvMessage;
END;

The three stages begin with neither diagnostic permission. The next stage grants only performance state. The last stage also grants security definition. Each stage tests partition metadata and a separate direct table SELECT. Effective permissions are recorded beside the actual outcomes.

EXEC dbo.PartitionPermissionTest N'Neither permission';

GRANT VIEW DATABASE PERFORMANCE STATE TO DiagnosticsDemo;
EXEC dbo.PartitionPermissionTest N'Performance-state only';

GRANT VIEW SECURITY DEFINITION TO DiagnosticsDemo;
EXEC dbo.PartitionPermissionTest N'Both permissions';

These grants apply to the role inside the demo database. They do not grant table SELECT. The procedure reverts to the original user after each test. The loginless user needs no server login.

Compare the expected pair with the observed operation

With neither permission, the controlled run returned error 262 for partition statistics. Its natural message named the missing performance-state permission. Direct table SELECT separately returned error 229. The errors identify different attempted operations.

The message reads VIEW DATABASE PERFORMANCE STATE permission denied in database 'PartitionPermissionDemo'.

After only the performance-state grant, that same restricted user received one partition-metadata row. The row reported index 1, partition 1, row_count 3 and used_page_count 2. No security-definition grant had yet been applied. Direct table SELECT still returned error 229.

These are all three stages. NULL DMV error means that operation returned its measured row. A missing row count in the first stage accompanies its actual permission denial. The direct SELECT error remains separate.

The test covered all three stages. Both metadata-reading stages returned the controlled partition row. The final stage held both expected permissions. Every direct table SELECT remained denied with error 229.

The measured permission bits distinguish the two successful metadata stages. Performance-state-only has security permission zero. The paired stage has security permission one. These values belong to the same restricted user in this exact test. Other engine builds may behave differently.

Metadata access and table SELECT answer different questions

Partition metadata is not the business-row result of a table SELECT. This test keeps both operations visible. It also records the known object ID and effective permission bits. A successful DMV read alone cannot describe every permission or access path held by an identity.

The row_count column in this DMV is approximate. The reported three matches the controlled table in this test. That agreement is an observed value, not a general exact-count guarantee. The metadata also includes storage information such as used_page_count.

Keep the result attached to its measured scope

Different role memberships, ownership and broader grants can change access. Metadata visibility can also produce missing rows or NULL values. Preserve actual errors and known object identifiers when comparing results. An unexplained empty result is different from an explicit permission denial.

USE master;
GO
DROP DATABASE PartitionPermissionDemo;

Record the engine build, identity, grants and exact operation together, then repeat the test for the context you need.

An expected permission pair is not a guarantee, it is a claim to test on your build.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.