Granting Execute permission to all SP

Was shown a quick way to grant Execute permission to SP today by a colleague, it seems i’ve been doing it the hard way all this time.

Imagine  i had this list of SP.


the list still goes longer than that. now the story is that i need to give execute permissions to those SP, and the way that i know is to do this.

grant execute on [dbo].[yaf_board_list] to user

or by doing this

select 'grant execute on ' + 
    QuoteName(specific_schema) + '.' +
    QuoteName(specific_name) + ' to user'
from information_schema.routines

i would get this


copy paste that on the sql editor and life is good.

OR,  as my colleague showed to me

i could do this instead

  • Right click on the database, choose properties
  • From permissions, search the user that you want to give permission
  • then on the Execute row, tick the grant checkbox
  • click ok and the permissions is setup for that user


now life is even better!.