63 lines
2.2 KiB
SQL
63 lines
2.2 KiB
SQL
-- Find what's different between working and non-working users
|
|
-- Run against PowderCoatingDb
|
|
|
|
-- Compare all three users
|
|
SELECT
|
|
Email,
|
|
UserName,
|
|
-- Check for null or empty critical fields
|
|
CASE WHEN PasswordHash IS NULL THEN 'NULL'
|
|
WHEN LEN(PasswordHash) = 0 THEN 'EMPTY'
|
|
ELSE CAST(LEN(PasswordHash) AS VARCHAR) END as PasswordHashStatus,
|
|
CASE WHEN SecurityStamp IS NULL THEN 'NULL'
|
|
WHEN LEN(SecurityStamp) = 0 THEN 'EMPTY'
|
|
ELSE CAST(LEN(SecurityStamp) AS VARCHAR) END as SecurityStampStatus,
|
|
CASE WHEN ConcurrencyStamp IS NULL THEN 'NULL'
|
|
WHEN LEN(ConcurrencyStamp) = 0 THEN 'EMPTY'
|
|
ELSE CAST(LEN(ConcurrencyStamp) AS VARCHAR) END as ConcurrencyStampStatus,
|
|
NormalizedUserName,
|
|
NormalizedEmail,
|
|
CompanyRole,
|
|
CompanyId
|
|
FROM AspNetUsers
|
|
WHERE Email IN ('admin@powdercoating.com', 'admin@demo.com')
|
|
OR UserName = 'superadmin'
|
|
ORDER BY Email;
|
|
|
|
-- Check for any stored claims that might be corrupted
|
|
SELECT
|
|
u.Email,
|
|
uc.ClaimType,
|
|
uc.ClaimValue,
|
|
LEN(uc.ClaimValue) as ValueLength,
|
|
-- Check if value contains special characters
|
|
CASE
|
|
WHEN uc.ClaimValue LIKE '%[^a-zA-Z0-9@._-]%' THEN 'Contains special chars'
|
|
ELSE 'OK'
|
|
END as ValidationStatus
|
|
FROM AspNetUserClaims uc
|
|
INNER JOIN AspNetUsers u ON u.Id = uc.UserId
|
|
WHERE u.Email IN ('admin@powdercoating.com', 'admin@demo.com')
|
|
OR u.UserName = 'superadmin';
|
|
|
|
-- Check user tokens (these can also cause issues)
|
|
SELECT
|
|
u.Email,
|
|
ut.LoginProvider,
|
|
ut.Name,
|
|
LEN(ut.Value) as ValueLength
|
|
FROM AspNetUserTokens ut
|
|
INNER JOIN AspNetUsers u ON u.Id = ut.UserId
|
|
WHERE u.Email IN ('admin@powdercoating.com', 'admin@demo.com')
|
|
OR u.UserName = 'superadmin';
|
|
|
|
-- Check for any unusual characters in key fields
|
|
SELECT
|
|
Email,
|
|
CASE WHEN Email LIKE '%[^a-zA-Z0-9@._-]%' THEN 'SUSPICIOUS' ELSE 'OK' END as EmailCheck,
|
|
CASE WHEN UserName LIKE '%[^a-zA-Z0-9@._-]%' THEN 'SUSPICIOUS' ELSE 'OK' END as UserNameCheck,
|
|
CASE WHEN CompanyRole LIKE '%[^a-zA-Z0-9]%' THEN 'SUSPICIOUS' ELSE 'OK' END as CompanyRoleCheck
|
|
FROM AspNetUsers
|
|
WHERE Email IN ('admin@powdercoating.com', 'admin@demo.com')
|
|
OR UserName = 'superadmin';
|