title: SQL Server 2012: How to set up table and row-level security (access control) for x user roles in y divisions?
body: <p>I have <strong>n user roles</strong> that shall <strong>restrict the degree of access</strong>, e. g.</p>
  
  <ol>
  <li>Reader (read only)</li>
  <li>Author (select, insert, update, delete)</li>
  <li>Maintainer (e. g. create new tables or views for their organisational unit)</li>
  </ol>
  
  <p>in <strong>each</strong> of our <strong>m organisational units</strong> (divisions), e. g. sales departments for the countries</p>
  
  <ol>
  <li>USA</li>
  <li>JAPAN</li>
  <li>United Kingdom</li>
  <li>Germany</li>
  </ol>
  
  <p>which shall <strong>restrict the visibiblity</strong> of tables and data rows (row-level security).</p>
  
  <p><strong>Question: What is the best set up to fulfill my access control requirements</strong></p>
  
  <ul>
  <li>without creating too many logins, DB users or DB roles (e. g. creating the full matrix of n * m is not an option, n + m would be perfect).</li>
  <li>allow users to see their tables to use standard query/reporting tools (views are acceptable)</li>
  <li>to minimize the DB changes required for user changes (I am using an Active Directory)</li>
  <li>to limit the creation of new tables by the maintainer user role to e. g. the (country?) schemas s/he has access to?</li>
  </ul>
  
  <p>I thought of using database roles based on windows AD groups but do not see a way to avoid the n * m explosion of roles (and therefor DB logins to reference the AD groups).</p>
  
  <p>Note: I saw a similar question but it is related to group organisational units into (overlapping) hierarchies (but did not differentiate between user roles):</p>
  
  <p><a href="https://stackoverflow.com/questions/14678367/sql-server-row-level-security-many-to-many">SQL Server row level security - many to many</a></p>
  
  <p>I am using the Microsoft SQL Server 2012 and user and groups are maintained in an Active Directory...</p>
  
  <p>A solution like described in in the white paper of <a href="https://stackoverflow.com/questions/11136109/row-level-security-in-sql-server-2012">Row level security in SQL Server 2012</a> seems to be too complicated for me (until it is the only option ;-)</p>