When I execute the IndexOptimize procedure with a user who isn't db_owner or without grant view database performance state.
I have an error message.
The user grant is :
CREATE USER [xxxx@xxx.xx] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE db_datareader ADD MEMBER [xxxx@xxx.xx]
ALTER ROLE db_datawriter ADD MEMBER [xxxx@xxx.xx]
ALTER ROLE db_ddladmin ADD MEMBER [xxxx@xxx.xx]
GRANT EXECUTE TO [xxxx@xxx.xx]
GRANT SHOWPLAN TO [xxxx@xxx.xx]
GO
When I execute manualy alter index xxxx on xxx rebuild it's OK.
Please, Can you explain me ?
Thank you for your help
Emmanuel
SQL Server version and edition
Microsoft SQL Azure (RTM) - 12.0.2000.8
Feb 23 2022 11:32:53
Copyright (C) 2021 Microsoft Corporation
Version of the script
Version: 2022-01-02 13:58:13
Date and time: 2022-05-31 13:48:15
Database: [OADR]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Msg 50000, Level 16, State 1, Procedure dbo.IndexOptimize, Line 1912 [Batch Start Line 0]
Msg 297, The user does not have permission to perform this action.
Msg 50000, Level 16, State 1, Procedure dbo.IndexOptimize, Line 1912 [Batch Start Line 0]
Msg 297, The user does not have permission to perform this action.
Date and time: 2022-05-31 13:48:15
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000002_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000003_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000004_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000005_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000008_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000009_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_0000000A_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000012_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16
Database context: [OADR]
Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000013_778AC167]
Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2022-05-31 13:48:16
Description of the issue
When I execute the IndexOptimize procedure with a user who isn't db_owner or without grant view database performance state. I have an error message.
The user grant is :
CREATE USER [xxxx@xxx.xx] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo; ALTER ROLE db_datareader ADD MEMBER [xxxx@xxx.xx] ALTER ROLE db_datawriter ADD MEMBER [xxxx@xxx.xx] ALTER ROLE db_ddladmin ADD MEMBER [xxxx@xxx.xx] GRANT EXECUTE TO [xxxx@xxx.xx] GRANT SHOWPLAN TO [xxxx@xxx.xx] GO
When I execute manualy alter index xxxx on xxx rebuild it's OK.
Please, Can you explain me ?
Thank you for your help
Emmanuel
SQL Server version and edition Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 23 2022 11:32:53 Copyright (C) 2021 Microsoft Corporation
Version of the script Version: 2022-01-02 13:58:13
What command are you executing?
EXECUTE [dbo].[IndexOptimize] @LogToTable = 'Y', @Databases = 'OADR', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 1, @FragmentationLevel2 = 15, @UpdateStatistics = 'ALL', @LockTimeout = '120', @LockMessageSeverity='10', @FillFactor=100, @Execute='Y', @Indexes = 'OADR.dbo.Meteohisto'
What output are you getting? Date and time: 2022-05-31 13:48:15 Server: sqlservertnq933-rec Version: 12.0.2000.8 Edition: SQL Azure Platform: Windows Procedure: [OADR].[dbo].[IndexOptimize] Parameters: @Databases = 'OADR', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 1, @FragmentationLevel2 = 15, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = 100, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'OADR.dbo.Meteohisto', @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = 120, @LockMessageSeverity = 10, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'Y', @Execute = 'Y' Version: 2022-01-02 13:58:13 Source: https://ola.hallengren.com
Date and time: 2022-05-31 13:48:15 Database: [OADR] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL
Msg 50000, Level 16, State 1, Procedure dbo.IndexOptimize, Line 1912 [Batch Start Line 0] Msg 297, The user does not have permission to perform this action.
Msg 50000, Level 16, State 1, Procedure dbo.IndexOptimize, Line 1912 [Batch Start Line 0] Msg 297, The user does not have permission to perform this action.
Date and time: 2022-05-31 13:48:15 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000002_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000003_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000004_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000005_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:15
Date and time: 2022-05-31 13:48:15 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000008_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:01 Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000009_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_0000000A_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000012_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16 Database context: [OADR] Command: SET LOCK_TIMEOUT 120000; UPDATE STATISTICS [dbo].[MeteoHisto] [_WA_Sys_00000013_778AC167] Comment: ObjectType: Table, IndexType: Column, Incremental: N, RowCount: 42675114, ModificationCounter: 0 Outcome: Succeeded Duration: 00:00:00 Date and time: 2022-05-31 13:48:16
Date and time: 2022-05-31 13:48:16
Completion time: 2022-05-31T15:48:16.5601556+02:00