SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_UserRolesClaims]
AS
/*


   Mod: 2023.05.26  agg. campi Claims
*/

SELECT 
     NU.Id AS UserId
    ,NU.UserName
    ,NU.Email
    ,ISNULL(UR.RoleId, '') AS RoleId
    ,ISNULL(ANR.Name, '')  AS RoleName
    ,UC.Id        AS ClaimId
    ,UC.UserId    AS ClaimUserId
    ,UC.ClaimType 
    ,UC.ClaimValue
    --,ANR.Id
    --,ANR.NormalizedName
    --,ANR.ConcurrencyStamp
    --,UR.UserId 
    --,NU.NormalizedUserName
    --,NU.NormalizedEmail
    --,NU.EmailConfirmed
    --,NU.PasswordHash
    --,NU.SecurityStamp
    --,NU.ConcurrencyStamp AS Expr2
    --,NU.PhoneNumber
    --,NU.PhoneNumberConfirmed
    --,NU.TwoFactorEnabled
    --,NU.LockoutEnd
    --,NU.LockoutEnabled
    --,NU.AccessFailedCount
FROM dbo.AspNetRoles ANR
INNER JOIN dbo.AspNetUserRoles UR         ON ANR.Id     = UR.RoleId
RIGHT OUTER JOIN dbo.AspNetUsers NU       ON UR.UserId  = NU.Id
LEFT OUTER JOIN dbo.AspNetUserClaims UC   ON NU.Id      = UC.UserId

GO