It can be done for a particular login using resource governer but not for a query or a SP.What you do basically is create resource pool and assign that resource pool to that particular login.

So any query or SP that is run with that particular login will only have memory resources defined in resource governer .