title: Quick way to grant Exec permissions to DB role for many stored procs
body: <p>Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to <strong><em>n</em></strong> stored procedures.</p>
  
  <p>When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role.</p>
  
  <p><a href="https://i.stack.imgur.com/hRALl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hRALl.png" alt="SQL Management Studio"></a>
  </p>
  
  <p>Here are the steps to apply permissions:</p>
  
  <ul>
  <li>select the object that you want to grant/deny permissions in the list of <em>Securables</em>. </li>
  <li>navigate to the list of <em>Explicit Permissions</em> below. </li>
  <li>select the Grant or Deny checkbox as appropriate.</li>
  </ul>
  
  <p>Repeat the above for <strong><em>n</em></strong> objects. Fire up some music to keep yourself entertained while doing this for 100+ objects! There's got to be a better way! It's a clickfest of major proportions.</p>
  
  <p><strong>Question</strong>: </p>
  
  <p>Is there a faster way to perform this task using SQL Server Management Studio 2005? Perhaps another GUI tool (preferably free)?</p>
  
  <p>Any suggestions for creating T-SQL scripts to automatically perform this task? i.e. create a table of all stored procedure names, loop, and apply the exec permissions?</p>