create_aspnet_views.sql

-- Create Views to map ASP.NET Identity tables to your custom tables
-- This allows ASP.NET Identity to work with your existing database schema

-- Drop views if they exist
IF EXISTS (SELECT * FROM sys.views WHERE name = 'AspNetUsers')
    DROP VIEW AspNetUsers;
IF EXISTS (SELECT * FROM sys.views WHERE name = 'AspNetRoles')
    DROP VIEW AspNetRoles;
IF EXISTS (SELECT * FROM sys.views WHERE name = 'AspNetUserRoles')
    DROP VIEW AspNetUserRoles;
GO

-- Create AspNetUsers view mapping to Users table
CREATE VIEW AspNetUsers AS
SELECT 
    Id,
    Email AS UserName,
    UPPER(Email) AS NormalizedUserName,
    Email,
    UPPER(Email) AS NormalizedEmail,
    CAST(1 AS BIT) AS EmailConfirmed,
    PasswordHash,
    CAST(NEWID() AS NVARCHAR(MAX)) AS SecurityStamp,
    CAST(NEWID() AS NVARCHAR(MAX)) AS ConcurrencyStamp,
    PhoneNumber,
    CAST(0 AS BIT) AS PhoneNumberConfirmed,
    CAST(0 AS BIT) AS TwoFactorEnabled,
    CAST(NULL AS DATETIMEOFFSET(7)) AS LockoutEnd,
    CAST(1 AS BIT) AS LockoutEnabled,
    CAST(0 AS INT) AS AccessFailedCount
FROM Users;
GO

-- Create AspNetRoles view mapping to Roles table
CREATE VIEW AspNetRoles AS
SELECT 
    CAST(Id AS NVARCHAR(450)) AS Id,
    RoleName AS Name,
    UPPER(RoleName) AS NormalizedName,
    CAST(NEWID() AS NVARCHAR(MAX)) AS ConcurrencyStamp
FROM Roles;
GO

-- Create AspNetUserRoles view mapping to UserRoles table
CREATE VIEW AspNetUserRoles AS
SELECT 
    UserId,
    CAST(RoleId AS NVARCHAR(450)) AS RoleId
FROM UserRoles;
GO

-- Test the views
SELECT 'AspNetUsers View:' AS ViewTest;
SELECT TOP 5 * FROM AspNetUsers;

SELECT 'AspNetRoles View:' AS ViewTest;
SELECT * FROM AspNetRoles;

SELECT 'AspNetUserRoles View:' AS ViewTest;
SELECT TOP 5 * FROM AspNetUserRoles;