Samuele Locatelli
46 lines
1009 B
Transact-SQL
46 lines
1009 B
Transact-SQL
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 |