RBA_Get_PermissionsByRole

RBA_Get_PermissionsByRole.sql

CREATE PROCEDURE [dbo].[RBA_Get_PermissionsByRole]
	@RoleId INT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT p.id as PermissionId, p.Name, p.[key] as [Key], parent.[key] as ParentKey, p.MaxBits, p.MinBits, rp.PermissionBit, dp.[key] as [Dependency]
	FROM role_permission rp WITH (NOLOCK)
	JOIN permission p WITH (NOLOCK) ON rp.permissionid = p.id
	LEFT JOIN permission parent WITH (NOLOCK) ON p.parentid = parent.id
	LEFT JOIN permissiondependency dep WITH (NOLOCK) ON p.id = dep.permissionid
	LEFT JOIN permission dp WITH (NOLOCK) ON dep.dependentpermissionid = dp.id	
	WHERE rp.Roleid = @RoleId
	AND p.id IN 
	(
		SELECT permissionid 
		FROM company_permission cp WITH (NOLOCK)
		JOIN role r WITH (NOLOCK) ON cp.companyid = r.companyid
		WHERE r.id = @roleid
	)
END