Grant Execute Permission to a User on all non-system stored procedures

We could easily grant execute permission to a user on all stored procedures on a database as below.

But unfortunately, our database will be at risk since the user would have got access to almost all stored procedures including the system stored procedures in that database. Hence use below steps to grant access to non-system stored procedures securely.

  1. Run below script on the database and copy the output.
  2. Paste the output to a new query window.
  3. Add “USE <database name>” at the beginning of the pasted output code and execute.

Note: Values inside ‘<‘ and ‘>’ has to be replaced with your actual database name and user information where ever required.

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply