0

Is it possible to control permissions on a single stored procedure level in SQL Server 2012? It seems that SQL Server Manager doesn't expose such functionality via GUI. Can somebody advice me how to view/set permission on a single stored procedure level?
In particular I'm interested in removing "Connect" right on one procedure for specified account while leaving Connect rights for this account on all the others procedures. Is it possible?

  • 2
    Connect is not a permission for procedures, only the database. Are you referring to execute permissions? – Shawn Melton Nov 17 '14 at 12:13
  • OK got it already. I just wanted to "hide" procedure from app which connects via connection string and enumerates all the objects from the database. I guess maybe deny "View definition" could do the trick? – Mikhail Nov 17 '14 at 12:33
2

You just need to deny EXECUTE permissions on any object that you don't want the app login to see. Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as that login is concerned.

DENY EXECUTE ON SchemaName.ProcName TO [AppLogin];

Once the DENY has been run, the following won't return anything for that login:

SELECT OBJECT_ID('SchemaName.ProcName');

SELECT *
FROM   sys.objects so
WHERE  so.name = 'ProcName';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.