VIEW SERVER STATE permission was denied on object 'server', database 'master'

Question

I am querying my database from an application and the following syntax is executable

SELECT t.name AS TableName
    ,i.name AS indexName
    ,sum(p.rows) AS RowCounts
    ,sum(a.total_pages) AS TotalPages
    ,sum(a.used_pages) AS UsedPages
    ,sum(a.data_pages) AS DataPages
    ,(sum(a.total_pages) * 8) / 1024 AS TotalSpaceMB
    ,(sum(a.used_pages) * 8) / 1024 AS UsedSpaceMB
    ,(sum(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name NOT LIKE 'dt%'
    AND i.object_id > 255
    AND i.index_id <= 1
GROUP BY t.name
    ,i.object_id
    ,i.index_id
    ,i.name
ORDER BY object_name(i.object_id)

However, the following code fails to execute due to permissions, I cannot change the permissions nor grant any permisions to the user connecting from the application. I'd like to understand which join/table is failing to query so that I can remove the column or join from the syntax, or is there an alternative way to write the query to get around the error.

    SELECT TOP 10
  tables.name AS table_name,
  CAST(ROUND(((SUM(allocation_units.total_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS total_space_mb,
  CAST(ROUND(((SUM(allocation_units.used_pages) * 8) / 1024.00), 2) AS numeric(36, 2)) AS used_space_mb,
  CAST(ROUND(((SUM(allocation_units.total_pages) - SUM(allocation_units.used_pages)) * 8) / 1024.00, 2) AS numeric(36, 2)) AS unused_space_mb,
  COUNT(DISTINCT indexes.index_id) AS indexes_count,
  MAX(dm_db_partition_stats.row_count) AS row_count,
  iif(MAX(ISNULL(user_seeks, 0)) = 0 AND MAX(ISNULL(user_scans, 0)) = 0 AND MAX(ISNULL(user_lookups, 0)) = 0, 1, 0) AS no_reads,
  iif(MAX(ISNULL(user_updates, 0)) = 0, 1, 0) AS no_writes,
  MAX(ISNULL(user_seeks, 0)) AS user_seeks,
  MAX(ISNULL(user_scans, 0)) AS user_scans,
  MAX(ISNULL(user_lookups, 0)) AS user_lookups,
  MAX(ISNULL(user_updates, 0)) AS user_updates,
  MAX(last_user_seek) AS last_user_seek,
  MAX(last_user_scan) AS last_user_scan,
  MAX(last_user_lookup) AS last_user_lookup,
  MAX(last_user_update) AS last_user_update,
  MAX(tables.create_date) AS create_date,
  MAX(tables.modify_date) AS modify_date
FROM sys.tables
LEFT JOIN sys.indexes
  ON tables.object_id = indexes.object_id
LEFT JOIN sys.partitions
  ON indexes.object_id = partitions.object_id
  AND indexes.index_id = partitions.index_id
LEFT JOIN sys.allocation_units
  ON partitions.partition_id = allocation_units.container_id
LEFT JOIN sys.dm_db_index_usage_stats
  ON tables.object_id = dm_db_index_usage_stats.object_id
  AND indexes.index_id = dm_db_index_usage_stats.index_id
LEFT JOIN sys.dm_db_partition_stats
  ON tables.object_id = dm_db_partition_stats.object_id
  AND indexes.index_id = dm_db_partition_stats.index_id
GROUP BY tables.name
ORDER BY 2 DESC

26/08/2022 00:00:05 js  SCR-160012 JavaScript: error while evaluating script 'adbDatabaseAnalysis/js'.
26/08/2022 00:00:05 js  r_seek,    max(last_user_scan) as last_user_scan,    max(last_user_lookup) as last_user_lookup,    max(last_user_update) as last_user_update,    max(tables.create_date) as create_date,    max(tables.modify_date) as modify_date  from    sys.tables   left 
26/08/2022 00:00:05 js  join sys.indexes on tables.object_id = indexes.object_id    left join sys.partitions on indexes.object_id = partitions.object_id    and indexes.index_id = partitions.index_id    left join sys.allocation_units on partitions.partition_id = allocation_units
26/08/2022 00:00:05 js  .container_id    left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id    and indexes.index_id = dm_db_index_usage_stats.index_id    left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.o
26/08/2022 00:00:05 js  bject_id    and indexes.index_id = dm_db_partition_stats.index_id  group by    tables.name  order by    2 DESC' could not be executed.
26/08/2022 00:00:05 js  und(       (         (           sum(allocation_units.used_pages) * 8         ) / 1024.00       ),        2     ) as numeric(36, 2)   ) as used_space_mb,    cast(     round(       (         (           sum(allocation_units.total_pages) - sum(allocation_u
26/08/2022 00:00:05 js  nits.used_pages)         ) * 8       ) / 1024.00,        2     ) as numeric(36, 2)   ) as unused_space_mb,    count(distinct indexes.index_id) as indexes_count,    max(     dm_db_partition_stats.row_count   ) as row_count,    iif(     max(       isnull(u
26/08/2022 00:00:05 js  ser_seeks, 0)     ) = 0      and max(       isnull(user_scans, 0)     ) = 0      and max(       isnull(user_lookups, 0)     ) = 0,      1,      0   ) as no_reads,    iif(     max(       isnull(user_updates, 0)     ) = 0,      1,      0   ) as no_writes, 
26/08/2022 00:00:05 js     max(     isnull(user_seeks, 0)   ) as user_seeks,    max(     isnull(user_scans, 0)   ) as user_scans,    max(     isnull(user_lookups, 0)   ) as user_lookups,    max(     isnull(user_updates, 0)   ) as user_updates,    max(last_user_seek) as last_use
26/08/2022 00:00:05 js  ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. SQLState: 37000
26/08/2022 00:00:05 js  WDB-200001 SQL statement 'select top 10  tables.name as table_name,    cast(     round(       (         (           sum(allocation_units.total_pages) * 8         ) / 1024.00       ),        2     ) as numeric(36, 2)   ) as total_space_mb,    cast(     ro
26/08/2022 00:00:05 js  ODB-240000 ODBC error: [Microsoft][SQL Server Native Client 11.0][SQL Server]VIEW SERVER STATE permission was denied on object 'server', database 'master'. SQLState: 37000

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Aug 25 '22 at 23:11
but the first query works on sys.tables sys.indexes sys.partitions sys.allocation_units it doesnt return error. — David Garcia, Aug 25 '22 at 23:14
Interesting, it has access to `sys.dm_db_partition_stats` but not `sys.dm_db_index_usage_stats` — David Garcia, Aug 25 '22 at 23:29
@DaleK That is a complete fallacy: many `sys` tables/views actually live on the current database. *Certain* ones live in `master`, and not all of them need `VIEW SERVER STATE`, for example `sys.databases`. — Charlieface, Aug 26 '22 at 00:19

score 1 · Accepted Answer · answered Aug 26 '22 at 00:22

Documentation for sys.dm_db_index_usage_stats says:

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Azure Active Directory admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Meanwhile, sys.dm_db_partition_stats can also be problematic, although it doesn't require server level permissions

Permissions

Requires VIEW DATABASE STATE and VIEW DEFINITION permissions to query the sys.dm_db_partition_stats dynamic management view. For more information about permissions on dynamic management views, see Dynamic Management Views and Functions (Transact-SQL).

The other views should be OK, but they will only show objects that the user has permissions on.

VIEW SERVER STATE permission was denied on object 'server', database 'master'

1 Answers1