title: SQL Server: How to permission schemas?
body: <p>Inspired by various schema related questions I've seen...</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/ms188676.aspx" rel="noreferrer">Ownership chaining</a> allows me to GRANT EXECUTE on a stored procedure without explicit permissions on tables I use, if both stored procedure and tables are in the same schema.</p>
  
  <p>If we use separate schemas then I'd have to explicitly GRANT XXX on the the different-schema tables. The ownership chaining example demonstrates that. This means the stored proc executing user can read/write your tables directly.</p>
  
  <p>This would be like having direct access to your instance variables in a class, bypassing getter/setters, breaking encapsulation.</p>
  
  <p>We also use row level security to restrict what someone sees and we apply this in the stored procedures.</p>
  
  <p>So, how can we maintain schema separation and prevent direct table access? </p>
  
  <p>Of course, the question won't apply if you use an ORM or don't use stored procs. But I'm <em>not</em> asking if I should use an ORM or stored proc in case anyone feels the need to enlighten me...</p>
  
  <p>Edit, example</p>
  
  <pre><code>CREATE USER OwnsMultiSchema WITHOUT LOGIN
  GO
  CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema
  GO
  CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema
  GO
  
  CREATE USER OwnsOtherSchema WITHOUT LOGIN
  GO
  CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema
  GO
  
  CREATE TABLE MultiSchema1.T1 (foo int)
  GO
  CREATE TABLE MultiSchema2.T2 (foo int)
  GO
  CREATE TABLE OtherSchema.TA (foo int)
  GO
  
  CREATE PROC MultiSchema1.P1
  AS
  SELECT * FROM MultiSchema1.T1
  SELECT * FROM MultiSchema2.T2
  SELECT * FROM OtherSchema.TA
  Go
  EXEC AS USER = 'OwnsMultiSchema'
  GO
  --gives error on OtherSchema
  EXEC MultiSchema1.P1
  GO
  REVERT
  GO
  
  CREATE PROC OtherSchema.PA
  AS
  SELECT * FROM MultiSchema1.T1
  SELECT * FROM MultiSchema2.T2
  SELECT * FROM OtherSchema.TA
  Go
  GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema
  GO
  EXEC AS USER = 'OwnsMultiSchema'
  GO
  --works
  EXEC OtherSchema.PA
  GO
  REVERT
  GO
  </code></pre>
  
  <p>Edit 2:</p>
  
  <ul>
  <li>We don't use "cross database ownership chaining"</li>
  <li>Row level security is a red herring and irrelevant: we don't use it everywhere</li>
  </ul>