SQL Server 2016及更高版本中ALTER RESOURCES服务器权限管控哪些权限

Alright, let's break down what the ALTER RESOURCES server-level permission grants and exactly what it controls in SQL Server 2016 and newer versions.

First, the basics: ALTER RESOURCES is a server-wide permission — granting it to a login or server role gives them the ability to modify core resource-related configurations for the entire SQL Server instance.

Here's the specific set of operations this permission enables:

• Modify server-level resource configuration options via sp_configure:

  • Adjust memory allocation settings like max server memory (MB) and min server memory (MB)
  • Tune parallel query behavior with max degree of parallelism (MAXDOP) and cost threshold for parallelism
  • Configure resource wait times using query wait
  • Adjust other resource-focused server settings (e.g., index create memory, min memory per query)

• Manage the Resource Governor infrastructure:

  • Create, alter, or drop resource pools with CREATE RESOURCE POOL, ALTER RESOURCE POOL, DROP RESOURCE POOL
  • Create, alter, or drop workload groups with CREATE WORKLOAD GROUP, ALTER WORKLOAD GROUP, DROP WORKLOAD GROUP
  • Assign or update the classifier function for Resource Governor using ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema.function)
  • Enable, disable, or reconfigure Resource Governor with ALTER RESOURCE GOVERNOR RECONFIGURE or ALTER RESOURCE GOVERNOR DISABLE

• Change default server file locations:

  • Modify the default storage paths for new database data files and transaction log files (either via SSMS or T-SQL)

A critical note: This is a high-impact permission. Accounts with ALTER RESOURCES can drastically alter the instance's performance, resource allocation, and stability. Always restrict this permission to trusted administrative roles only.

内容的提问来源于stack exchange，提问作者Adam