Grant non Sysadmin the ability to change max memory setting SQL Server I've received a request to create a stored procedure that would allow a user (with access to the DB server) to change SQL Server's max server memory (MB). This wasn't the hard part.

The other requirement is that this user should be able to change this only parameter max server memory (MB) and nothing else. What I'd like to know is, if this is possible.

I do realize that, to be able executesp_configure and runRECONFIGURE, the minimum privilege required is ALTER SETTINGS. And providing ALTER SETTINGS would defeat the purpose of the stored procedure (which would be designed to run just exec sys.sp_configure 'max server memory (MB)', @TheReqMemVal) along with a minimum and maximum allowed value for max server memory (MB).

After reading some of the comments, I'd like to add that if responses were to incorporate possible security pitfalls, it would be greatly appreciated.
You should use signed procedure in this case. 

The greate article Signing Procedures with Certificates by Erland Sommarskog explains it well, and here are the steps to perform:


Create a certificate in the master database.
Create a login for that certificate.
Grant that login ALTER SETTINGS.
Export the certificate to file.
Switch to the application database.
Import the certificate from the file.
Delete the file from disk.
Create a user for the certificate.
Sign the stored procedure with the certificate, each time you have
changed the procedure.