-- роли
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
)

PrincipalId int
TemplateId int

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 @RoleAssigments values (1, 1)
insert into @RoleAssigments values (1, 2)
insert into @RoleAssigments values (1, 3)

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


-- это не правильный запрос для примера
select 
	ass.PrincipalID,
	ass.RoleID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId

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

;with cte as 
	(select *, count(*)over(partition by TemplateId) as cntAll from @RoleTemplateToRole)
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join cte templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId,
	templToRole.cntAll
having count(*) = templToRole.cntAll

DECLARE @Principals table (PrincipalId int)
INSERT INTO @Principals (PrincipalId) VALUES (1), (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)