I'm trying to implement row level security permission to my data model. Because I'm using SQL Server 2012, I had to do this using views and users names joined with the views. (That's not the point but to those who are interested in I paste the link which helped me to get desired effect http://www.sqlservercentral.com/articles/Stairway+Series/128866/).
I've decided to restrict access using schemas permission. And everything works fine, however not for one scheme. My view includes selects to the tables in few schemes which I restricted. But, even when I disable access to the schema which is the problem (let's say it is "CDN" scheme), I cannot reference from my view to the table in the CDN scheme. I've found that this is the schema type "Database role" and the owner is CDN. If I change the owner of the CDN scheme to "dbo" which is "User" type, I can get to the schema in my view.
I don't know the difference between those types, but I wouldn't like to change the owner of CDN. Can someone give me any clues to solve to solve this problem? Thanks in advance. M.
I've found that when in roles >database roles of CDN in general tab I add schema from my view I can perform my view. But I don't know if this change is safe?
I may define the problem not correctly. I will redefine it:
My goal is to have restricted access to all tables and views, etc. in a database, except those in "Qlik" schema.
My views (in Qlik schema) connects to different schemas i.e. CDN, TBC, dbo (Qlik.MyCostumersView - sample view):
SELECT a.CustId, a.Name, a.City, a.CreditLimit, a.SocialSecurityNumber, a.FelonyConvictions, a.UserAccess FROM CDN.Customer a, TBC.Struktura b, dbo.MPKDzial
And I can't get my goal under any circumstances.
I've found that I can manage with my access between schemas which has the same oweners. However CDN has the CDN as the owner. When I change the ownership to CDN in my other schemas, it works fine, unfortunatelly except with dbo schema (dbo owner).
