2

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.

Improve this question
9
  • You can use EXECUTE AS instead of giving permissions.
    – McNets
    Commented Jul 14, 2017 at 8:33
  • @ McNets he cannot use execute as in this case: if he uses execute as CLAUSE of the sp, he can executes only as a USER, not login, and the permission he needs is a SERVER LEVEL permission; the use of EXECUTE AS statement can use the login, but it requires impersonate permission ot that login
    – sepupic
    Commented Jul 14, 2017 at 9:16
  • @sepupic only partially true. There are ways (read: don't do this) to elevate that user. See: sqlstudies.com/2014/02/26/…
    – George.Palacios
    Commented Jul 14, 2017 at 9:19
  • @George.Palacios do you mean setting TRUSTWORTHY on for the database? For providing all the db_owners the opportunity to raise themselves to sysadmin? I don't consider it a solution at all
    – sepupic
    Commented Jul 14, 2017 at 9:24
  • It is completely a solution to the issue at hand, but it does open up security holes. That's why I included the (read: don't do this)
    – George.Palacios
    Commented Jul 14, 2017 at 9:26

1 Answer 1

Reset to default
6

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.
Improve this answer
1
  • Thanks for the details, @sepupic. I'll have to try this on my test server and if I'm able to get it to work, I will mark this as the answer. However, implementing this in our prod environment is going to be a whole different battle (given that I'll have to look into any security concerns (if you are aware of any such, do update this post...nevertheless, I to appreciate your response, thanks again).
    – Santa
    Commented Jul 15, 2017 at 21:03

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.