title: SQL Server row level security - many to many
body: <p>We are using SQL Server 2008 and have our permissions setup in Active Directory (AD).  Each row in a core table needs to be only viewable by those that have permission to view each row.  I can setup row level security on each row (each row contains a single role that lines up with an AD group), however I have the following problem :-</p>
  
  <ul>
  <li>because the roles represent countries I have several 100 roles.  Further I have some users who have access to several roles...and the several roles given users have access to, are not grouped consistently (i.e. there is overlap) : hence role to user is a many to many relationship</li>
  </ul>
  
  <p>This being the case (to enable a flexible model) my first instinct is to create one AD group per country, then create the groupings within database roles (to group each country).  However this will be a maintenance overhead - for example there are many staff members who have access to all groups and hence when a country comes online, a new AD group is created which means I will have to request these users be added as members to the new AD group (as well a new global user needs to be added to all groups) - I would rather have a global group that has access across the board.</p>
  
  <p>Has anyone come across this kind of issue.  Basically I can accommodate the many to many within the database role level if I have very granular AD groups only, however I would rather for example have granular AD groups (for those users that require granular permissions) as well as a global AD group for those users that need access to all rows (that I only need to add users, who have global access)</p>