How is the “Adjust memory quotas for a process” user right assignment used in SQL Server 2019?

Posted on

Question :

I am looking at the Configure Windows Service Accounts and Permissions doc (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15) for SQL Server 2019 and it states that the SQL Service Account needs to have the Adjust memory quotas for a process permission.

Can anyone confirm why this is required and what it’s function is within the SQL Engine. In my Dev environment, I do not have it enabled and all seems to be functioning correctly.

Answer :

Can anyone confirm why this is required and what it’s function is within the SQL Engine

This privilege when granted to user account will give ability to the account to adjust maximum memory that other process can use. See the official document

This privilege determines who can change the maximum memory that can be consumed by a process. This privilege is useful for system tuning on a group or user basis.
This user right is defined in the Default Domain Controller Group Policy Object (GPO) and in the local security policy of workstations and servers.

The SQL Server, SQL Server agent. SSAS service account should have this privilege to adjust the quota if DB engine deems necessary. But also note that if this is given to user account which is used to run certain queries it can also lead to Denial of Service attack( DoS) by changing the quota.

Leave a Reply

Your email address will not be published. Required fields are marked *