title: SQL Server 2016 Execute Permission Denied
body: <p>I am running SQL Server 2016 and have setup role based access to control what users can do based on roles.</p>
  
  <p>I've setup a role:</p>
  
  <pre><code>Create Role [Developer]
  </code></pre>
  
  <p>I've granted permissions on that role to run SELECTS, buy deny INSERT, UPDATE and DELETE on all tables:</p>
  
  <pre><code>GRANT SELECT ON SCHEMA::dbo to [Developer]
  DENY INSERT, UPDATE, DELETE ON SCHEMA::dbo to [Developer]
  </code></pre>
  
  <p>I add users to the role:</p>
  
  <pre><code>ALTER ROLE [Developer] ADD MEMBER [SomeUser]
  </code></pre>
  
  <p>I want the user to execute only specific stored procedures, so I grant execute permissions on the specific sprocs to the role:</p>
  
  <pre><code>GRANT EXECUTE ON dbo.mySproc to [Developer]
  </code></pre>
  
  <p>However, when I login as SomeUser and try to execute the sproc, I am getting the following error:</p>
  
  <blockquote>
    <p>The EXECUTE permission was denied on the object 'mySproc', database 'MyDB', schema 'dbo'.</p>
  </blockquote>
  
  <p>Am I doing something wrong?</p>