0

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.

1
  • "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.
    Jan 24, 2023 at 18:03

1 Answer 1

0

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.

4
  • He's using 2019 not 2022.
    – J.D.
    Jan 26, 2023 at 5:34
  • In that case VIEW SERVER STATE is needed. 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.
    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 provided Jan 26, 2023 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.