title: SQL Server 2008 grant permission to information_schema.columns
body: <p>I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called <code>recControl_system_option</code> which in turn queries <code>Information_schema.columns</code>. </p>
  
  <p>The problem is that in this proc the query</p>
  
  <p><code>select column_name from information_schema.columns where table_name = 'recControl_manager'</code></p>
  
  <p>does not return any records. cctc_reader has grant permissions on:</p>
  
  <ul>
  <li>each select proc</li>
  <li><code>recControl_system_option</code></li>
  </ul>
  
  <p>so in theory this should work. I have no problems when run under dbo. </p>
  
  <p>If I grant db_datareader to cctc_reader the query is fine, but I don't want to grant reader permissions to all tables (hence why I used stored procs). I've tried granting Select permissions on Information_schema in the Master db as suggested in some articles, but still can't get this to work.</p>
  
  <p>Any suggestions?</p>