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
CodePudding user response:
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 theVIEW 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
andVIEW DEFINITION
permissions to query thesys.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.