67 lines
1.8 KiB
Transact-SQL
67 lines
1.8 KiB
Transact-SQL
/*
|
|
select * from ASPNetusers
|
|
select * from ASPNetUserRoles
|
|
select * from ASPNetRoles
|
|
select * from AffiliateAdmin
|
|
|
|
select u.Id, u.FullName, u.Email, u.AffiliateId, r.Name as RoleName, a.[Key]
|
|
from ASPNetUsers u
|
|
LEFT JOIN ASPNetUserRoles ur on ur.UserId = u.Id
|
|
LEFT JOIN ASPNetRoles r on r.Id = ur.RoleId
|
|
LEFT JOIN AffiliateAdmin aa on u.Id = aa.ApplicationUserId
|
|
LEFT JOIN Affiliate a on aa.AffiliateId = a.Id
|
|
order by u.fullname
|
|
|
|
update aspnetuserroles set userid = lower(userid), roleid = lower(roleid)
|
|
update affiliateadmin set applicationuserid = lower(applicationuserid)
|
|
|
|
*/
|
|
|
|
DECLARE @UserEmail varchar(max), @AffiliateKey varchar(max), @RoleName varchar(max), @AffiliateId int, @RoleId uniqueidentifier, @UserId uniqueidentifier
|
|
|
|
SET @UserEmail = 'smcpeters@brb.org'
|
|
SET @AffiliateKey = 'wfgw'
|
|
--SET @RoleName = 'Administrator'
|
|
SET @RoleName = 'Affiliate Administrator'
|
|
|
|
SELECT @UserId = Id from ASPNetUsers where Email = @UserEmail
|
|
SELECT @RoleId = Id from ASPNetRoles where Name = @RoleName
|
|
SELECT @AffiliateId = Id from Affiliate where [Key] = @AffiliateKey
|
|
|
|
--SELECT LOWER(@UserId), LOWER(@RoleId), @AffiliateId
|
|
--RETURN
|
|
|
|
|
|
IF (@UserId is null OR @RoleId is null OR @AffiliateId is null)
|
|
BEGIN
|
|
PRINT 'Exiting: missing or invalid info'
|
|
RETURN
|
|
END
|
|
|
|
IF(@RoleName = 'Affiliate Administrator')
|
|
BEGIN
|
|
|
|
IF NOT EXISTS( SELECT 1
|
|
FROM AffiliateAdmin
|
|
WHERE AffiliateId = @AffiliateId AND ApplicationUserId = @UserId)
|
|
BEGIN
|
|
PRINT 'Adding AffiliateAdmin record'
|
|
INSERT INTO AffiliateAdmin VALUES(LOWER(@UserId), @AffiliateId, GETUTCDATE())
|
|
END
|
|
ELSE BEGIN
|
|
PRINT 'AffiliateAdmin record exists'
|
|
END
|
|
END
|
|
|
|
IF NOT EXISTS( SELECT 1
|
|
FROM ASPNetUserRoles
|
|
WHERE UserId = @UserId AND RoleId = @RoleId)
|
|
BEGIN
|
|
PRINT 'Adding UserRole'
|
|
INSERT INTO ASPNetUserRoles VALUES(LOWER(@UserId), LOWER(@RoleId))
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'UserRole already exists'
|
|
END
|