Role Permissions Filtered View t-sql

Question

Is it possible to filter the content of a t-sql view based on SELECT GRANTS assigned to the database roles of end users without generating SELECT permissions exceptions?

If so how?

Pseudo:

CREATE TABLE Beer(a(x), b(y)); GRANT SELECT ON Beer to BeerOnlyRole;
CREATE TABLE Wine(a(x), b(y)); GRANT SELECT ON Wine to WineAndBeerRole; 
GRANT SELECT ON Beer to WineAndBeerRole;

CREATE VIEW SimpleAlcoholSearch
 (
    SELECT  a  AS BrandName
           ,b  AS Strength
    FROM Beer

    UNION

    SELECT  a  AS BrandName
           ,b  AS Strength
     FROM Wine
 )

 GRANT SELECT ON SimpleAlcoholSearch to BeerOnlyRole;
 GRANT SELECT ON SimpleAlcoholSearch to WineAndBeerRole;

....

AS BeerOnlyRole : SELECT * FROM SimpleAlcoholSearch : BEER1 1% BEER2 2%

AS WineAndBeerRole : SELECT * FROM SimpleAlcoholSearch : BEER1 1% BEER2 2% WINE1 10% WINE2 11%

Thanks for reading this...

Answer 1

I solved this problem with a quick and dirty check on various catalog views for each table:

SELECT  a  AS BrandName
           ,b  AS Strength
    FROM Beer   

WHERE 1 = 

    (
         SELECT TOP 1 1
         FROM sys.database_permissions sy_dpe

         JOIN sys.objects sy_o ON sy_dpe.major_id = sy_o.object_id
         JOIN sys.schemas sy_sc ON sy_o.schema_id = sy_sc.schema_id
         JOIN sys.database_principals sy_dpr ON sy_dpe.grantee_principal_id = sy_dpr.principal_id
         JOIN sys.database_role_members sy_drm ON sy_drm.role_principal_id = sy_dpr.principal_id
         JOIN sys.database_principals sy_dpr2 ON sy_dpr2.principal_id = sy_drm.member_principal_id

         WHERE sy_dpr2.name = SYSTEM_USER
         AND sy_o.name = 'Beer'
         AND sy_sc.name = '[n]'
         AND sy_dpe.type = 'SL'
         AND sy_dpe.state = 'G'
    )

UNION

....

Anyone got a tidier approach?

Thanks