title: Row level security access across many schema SQL Server 2012
body: <p>I would like to secure access to rows in tables. The problem is that I need to connect to data between databases and different schemas. I can more or less handle with it when my data is in one database and schema. The problem starts in views when they need to get data from other database and schemas that has different owners.</p>
  
  <p>I've created a table where I granted acces to data depending on logins</p>
  
  <p>Like this:
  PermissionTab</p>
  
  <p><strong>DepId Login</strong></p>
  
  <p>1   Login1</p>
  
  <p>2   Login1</p>
  
  <p>3   Login1</p>
  
  <p>4   Login2</p>
  
  <p>5   Login3</p>
  
  <p>6   Login4</p>
  
  <p>7   Login5</p>
  
  <p>1   Login6</p>
  
  <p>2   Login6</p>
  
  <p>3   Login6</p>
  
  <p>4   Login6</p>
  
  <p>5   Login6</p>
  
  <p>I've set data in schemas CDN, TBC, dbo and Optima unaccessible (those schemas have different owners CDN - CDN - the rest dbo). I've created schema Qlik (dbo owner) (accessible to users) where are views that gets data from tables (in different schemas).
  To get row level security in my views I've added something like that.</p>
  
  <pre><code>Where dv.DepId in (Select DepId From Qlik.PermissionTab Where Login=USER_NAME() ) OR
      IS_ROLEMEMBER('db_owner') = 1 OR
      IS_SRVROLEMEMBER('sysadmin') = 1 
  </code></pre>
  
  <p>Those views must connect to many databases as well.</p>
  
  <p>This is a sample View
      [Qlik].[DekretPomocniczyView]
      Select * from
      [CDN].[DekretyNag] 
      inner join [CDN].[DekretyElem] on DeN_DeNId=DeE_DeNId
      left outer join [CDN].[DekretyKonta] on DeE_DeEId=DeK_DeEId
      left outer join Aplikacja.Qlik.MPKKontaView on DeK_AccId=Acc_AccId</p>
  
  <pre><code>Where (DepId in (Select DepId From Aplikacja.Qlik.PermissionTab Where
  Login=USER_NAME() ) OR IS_ROLEMEMBER('db_owner') = 1 OR
  IS_SRVROLEMEMBER('sysadmin') = 1
  </code></pre>
  
  <p>I've found it impossible to get row level permission using schemas and to connect between tables. Quite often the view from one database connects to view in another database, which also gets data from another database.</p>
  
  <p>Because the problem was urgent for me, after many tests I've found I can get the effect using roles and setting permissions only to specific views, not schemas. Now (after I've asked the question) I wonder If I could get it using schemas and if my model is correct and safe. This is SQL 2012.</p>