Con los siguientes Querys se puede consutlar la información del Rol base así como los roles temporales con lo que cuenta el asociado.
DECLARE @User NVARCHAR(10) = ‘750349’
Declare @Rol table (id nvarchar(10))
Select JobClass,Position,Tabadge,TermDate,PosAuth,JobTitle,RightHanded,EmpStatus,* from Employee where Payrollid = @User
–Consulta de Roles por Usuario
SELECT u.UserName, r.Name, ur.[ExpireDate],r.code
FROM AspNetUserRoles ur
INNER JOIN AspNetUsers u ON ur.UserId = u.Id AND u.Id = (SELECT id FROM AspNetUsers WHERE username = @User)
INNER JOIN AspNetRoles r ON ur.RoleId = r.Id
–Tabla temporal con los Roles del Usuario
Insert @Rol(id) SELECT r.code FROM AspNetUserRoles ur INNER JOIN AspNetUsers u ON ur.UserId = u.Id AND u.Id = (SELECT id FROM AspNetUsers WHERE username = @User) INNER JOIN AspNetRoles r ON ur.RoleId = r.Id
–Permisos con Roles de Usuario
SELECT ANR.Code, ANR.[Name], ANC.[Code], ANC.[Description], ANC.[Group], ANC.[Subgroup], ANCV.[Value]
FROM AspNetClaims ANC INNER JOIN AspNetClaimValues ANCV ON ANC.ID = ANCV.ClaimID INNER JOIN AspNetRoles ANR ON ANC.RoleID = ANR.ID
Where ANR.Code in (Select * From @Rol) AND ANC.[Code] = ‘TA0001’
ORDER BY ANR.Code
