Re-indexing Job (Dynamic Management View permissions?)

Question

Post reply

Re-indexing Job (Dynamic Management View permissions?)

richardn-671816

Old Hand

Points: 304
More actions
February 27, 2017 at 11:31 pm

#341082

I am trying to configure an agent job run SQL that performs an analysis of fragmentation on all user databases and based on that information performs the appropriate indexing (if any).
My current problem concerns execution of the following statement which inserts the results into a table:
set @sql=
'select ''' + CONVERT(VARCHAR(36),@indexLogId) + ''' AS indexLogId
        ,' + CONVERT(VARCHAR(1),@fragType) + ' AS FragType
        ,''' + @dbName + ''' as DBName
        ,QUOTENAME(s.name) AS SchemaName
        ,QUOTENAME(o.name) AS TableName
        ,QUOTENAME(i.name) AS IndexName
        ,stats.Index_type_desc AS IndexType
        ,stats.page_count AS IndexPageCount
        ,stats.partition_number AS IndexPartitionNumber
        ,CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE ' + CONVERT(VARCHAR(10),@DefaultFillFactor) + ' END AS [Fill Factor]
        ,stats.avg_fragmentation_in_percent
        ,stats.fragment_count
        ,CASE WHEN stats.index_level =0 THEN ''Leaf Level'' ELSE ''Nonleaf Level'' END AS IndexLevel
        ,CASE WHEN stats.avg_fragmentation_in_percent BETWEEN 10 AND 20 AND stats.page_count > 50000 THEN 1 ELSE
                CASE WHEN stats.avg_fragmentation_in_percent BETWEEN 20 AND 40 THEN 2 ELSE
                CASE WHEN stats.avg_fragmentation_in_percent > 40 THEN 3 ELSE 0
        END END END AS IndexFixAction
from    sys.dm_db_index_physical_stats(db_id(@dbname), NULL, NULL , NULL, ''LIMITED'') stats
        ,' + @dbname + '.sys.objects o
        ,' + @dbname + '.sys.schemas s
        ,' + @dbname + '.sys.indexes i
where
   o.OBJECT_ID = stats.OBJECT_ID
   AND s.schema_id = o.schema_id
   AND i.OBJECT_ID = stats.OBJECT_ID
   AND i.index_id = stats.index_id
   AND stats.avg_fragmentation_in_percent>= 10
   AND stats.page_count >= 1000
        AND QUOTENAME(o.name) NOT Like ''' + '%tombstone%' + '''
ORDER BY stats.avg_fragmentation_in_percent DESC,stats.page_count DESC'
Despite the above dbName variable, I am limiting my testing to a single database. *
The agent job owner is an SQL user with read/write access to that database.
I have granted access to VIEW DATABASE STATE for that user in that database.
I have granted SELECT access for that user to sys.dm_db_index_usage_stats (master db).
The error message being generated is: Error 297: The user does not have permission to perform this action.
* I have reduced the stored procedure in question so that it only runs a hard-coded version of this sql as a select (thus removing all other complexities within the stored proc -) and the error is still generated.
Any advice regarding what I'm clearly missing here would be much appreciated.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

From BOL:
Requires the following permissions:

CONTROL permission on the specified object within the database.
VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.
VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.

😎