-- роли
DECLARE @Roles TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- шаблоны назначения ролей
-- в шаблоне может быть несколько ролей
DECLARE @RoleTemplates TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- Template многие_ко_многим Role
DECLARE @RoleTemplateToRole TABLE
(
	TemplateId int,
	RoleId int
)


-- таблица назначения ролей
-- Principal многие_ко_многим Role 
DECLARE @RoleAssigments TABLE
(
   PrincipalId int,
   RoleId int
)
DECLARE @Principals table (PrincipalId int)
INSERT INTO @Principals (PrincipalId) VALUES (1), (2)


insert into @Roles values (1, 'Role1')
insert into @Roles values (2, 'Role2')
insert into @Roles values (3, 'Role3')

insert into @RoleTemplates values (1, 'Template1')

--insert into @RoleTemplateToRole values (1, 1)
--insert into @RoleTemplateToRole values (1, 2)
--insert into @RoleTemplateToRole values (1, 4)


insert into @RoleAssigments values (1, 1)
insert into @RoleAssigments values (1, 2)
insert into @RoleAssigments values (1, 3)
insert into @RoleAssigments values (1, 4)

insert into @RoleAssigments values (2, 1)
insert into @RoleAssigments values (2, 2)
insert into @RoleAssigments values (2, 3)

--	***	1	
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId
having count(*) = (select count(*) from @RoleTemplateToRole xz where xz.TemplateId = templToRole.TemplateId)

--	***	2	
SELECT  
    * 
FROM    
    @Principals p
    INNER JOIN 
        @RoleTemplates t
            ON NOT EXISTS (
                SELECT 
                    * 
                FROM 
                    @RoleTemplateToRole tr 
                    LEFT JOIN 
                        @RoleAssigments ra 
                            ON ra.PrincipalId = p.PrincipalId 
                            AND ra.RoleId = tr.RoleId
                WHERE tr.TemplateId = t.Id
                    AND ra.RoleId IS NULL)