What minimal permissions can be assigned to a database user so that user can read performance of indexes on tables/views in that database? I am using latest version of SQL Server and Azure SQL Managed Instance.
- "read performance of indexes" - Could you please elaborate what you mean by this?...Do you mean permissions to view the execution plan of a given query?J.D.– J.D.2023-01-24 18:03:16 +00:00Commented Jan 24, 2023 at 18:03
Add a comment |
1 Answer
Let me know if the new server role "##MS_ServerPerformanceStateReader##" on SQL 2022 helps you achieve what you are looking for. Please read here.
This new role allows any member to view key performance metrics through Dynamic Management Views (DMV) but not the same full rights as members of the sysadmin role. You no longer need to assign VIEW SERVER STATE permissions to users that need access to DMVs like sys.dm_db_index_usage_stats.
-
- In that case VIEW SERVER STATE is needed.Alberto Morillo– Alberto Morillo2023-01-26 12:38:12 +00:00Commented Jan 26, 2023 at 12:38
- Depends on what OP is trying to do, as my question on his Post asks. If he wants to read execution plans, yes. If he literally wants the metrics on what indexes are being used, then that's in the DMVs and he shouldn't need
VIEW SERVER STATE.J.D.– J.D.2023-01-26 13:27:54 +00:00Commented Jan 26, 2023 at 13:27 - He requested an answer from me on this dba.stackexchange.com/questions/316811/…. That is the reason I tried to provide my best answer with the information providedAlberto Morillo– Alberto Morillo2023-01-26 13:36:52 +00:00Commented Jan 26, 2023 at 13:36