User managment - app.users

DB Objects

• [app].[users] - users
• [app].[roles] - roles
• [app].[teams] - teams
  • [team_short] - short name
  • [team_name] - long name
  • [team_opts] - options for example pop3,smtp
  • [team_secret] - configuration
  • [team_type] - external/internal/mail
• [app].[user_claims]
• [app].[user_roles]
• [app].[role_claims]
• [app].[user_tokens]
• [app].[user_logins]
• [app].[user_teams]

Scripts

List all members of team

select 
  team_short,team_name,username 
  from app.teams  t
inner join app.user_teams ut on t.team_id =ut.team_id
inner join app.users u on ut.user_id = u.id
where 
  t.team_status > -1
  and  team_name ='edocuments'
order by 
  team_name,username

Insert team to all users

declare @teamname varchar(50) ='SYSTEM JBB'
declare @teamuser_created_by varchar(50) ='sys'

declare @teamid uniqueidentifier = (select team_id from [app].[teams] where team_name = @teamname)

if (@teamid is null)
begin
  RAISERROR ('Team not found', 16, 1);
  return
end

insert into [app].[user_teams] ( user_id, team_id, teamuser_created_by)
select u.id, @teamid, @teamuser_created_by
from [app].[users] u
where u.id not in ( select user_id from [app].[user_teams] where team_id =@teamid)

--set default
--update app.users set team_id = @teamid where team_id is null

Insert Claim(Role) to all users

--SET IDENTITY_INSERT [dbo].[AspNetUserClaims] ON;  

declare @role varchar(50) ='app_erp'

insert into [app].[user_claims]
( ClaimType, ClaimValue, UserId, claim_created_by)
select 
  'http://schemas.microsoft.com/ws/2008/06/identity/claims/role', 
  @role,
  id,
  'sys' 
from [app].[users]
where id not in ( select userid from [app].[user_claims] where ClaimValue =@role)


--SET IDENTITY_INSERT [dbo].[AspNetUserClaims] Off;  

Migration platformacrm > platforamerp

UTWORZENIE nowych użytkowników [app].[users] na podstawie starszych [users].[users]

/*TWORZENIE nowych użytkowników ( [app].[users]) na podstawie starszych ( [users].[users])*/
BEGIN TRAN
  Insert into app.users (id, AccessFailedCount, ConcurrencyStamp, email, EmailConfirmed, LockoutEnabled , NormalizedEmail, NormalizedUserName,PasswordHash,UserName, user_app_name)
  select lower(u.userID), 0, 'a8c89468-e55e-4280-bb1d-aba604afc627',  u.email, 0, 1, upper(u.email), upper(u.email),'xxxxxxxx', lower(u.email),'erp' from [user].[user] u
  where u.email is not null and u.email like '%@%' and u.email not in (select email from app.users where email =u.email)
ROLLBACK TRAN

SRS/852 Przeniesienie grup i przypisanych grup użytkowników na teamy

BEGIN TRAN
  INSERT INTO [app].[teams] (team_id, team_short, team_name,team_color, team_status) 
  SELECT g.groupID, g.groupShortcut, 'SYSTEM '  +g.groupName, g.groupColor, 0
  FROM [user].[group] g
  where not exists (select team_id from [app].[teams] t where g.groupID = t.team_id) and g.status > -1

  INSERT INTO [app].[user_teams] ([user_id], team_id) 
  SELECT g.userID, g.groupID
  FROM [user].[userGroup] g
  where not exists (select  teamuser_id from [app].[user_teams] t where g.groupID = t.team_id and g.userID = t.[user_id])
  and userID in (select id from app.users)

  -- usuneicei tych userow gdize nie ma uzytkownikow
  -- delete from app.user_teams where user_id not in (select id from app.users)
ROLLBACK TRAN

Dodanie wszystkich dostępnych ról dla konkretnego użytkownika.

BEGIN TRAN
  INSERT INTO [app].[user_teams] (user_id, team_id)
  select u.Id, t.team_id from [app].[teams] t, [app].[users] u
  where u.email = 'support@platformacrm.pl'
  and u.id not in (select top 1 user_id from [app].[user_teams] where user_id = u.id and team_id = t.team_id)
ROLLBACK TRAN

Optional - Skrypt migracyjny uzytkownikow miedzy formatwami na potrzby kontrahenta

/*skrypt migracyjny uzytkownikow miedzy formatwami na potrzby kontrahenta*/
Begin transaction
    declare @olduserid varchar(36) 
    declare @newuserid varchar(36) 
    declare @email varchar(100) 
    select top 1  @newuserid=LOWER(uu.userid) , @olduserid=au.id,@email= au.Email from app.users au left join [user].[user] uu  on au.Email = uu.email
    where cast(uu.userID as varchar(36)) != au.id
    and au.email ='marcin@platformacrm.pl'

    print @email
    print @olduserid
    print @newuserid

    ALTER TABLE app.user_claims NOCHECK CONSTRAINT FK_AspNetUserClaims_AspNetUsers_UserId;  
    ALTER TABLE app.user_tokens NOCHECK CONSTRAINT FK_AspNetUserTokens_AspNetUsers_UserId;  

    update app.users set id = @newuserid where  id=@olduserid
    update app.user_tokens set userid = @newuserid where  userid=@olduserid
    update app.user_teams set [user_id] = @newuserid where  [user_id]=@olduserid
    update app.user_roles set  userid = @newuserid where  userid=@olduserid
    update app.user_logins set  userid = @newuserid where  userid=@olduserid
    update app.user_claims set  userid = @newuserid where  userid=@olduserid
    update app.user_devices set  userid = @newuserid where  userid=@olduserid

    ALTER TABLE app.user_claims CHECK CONSTRAINT FK_AspNetUserClaims_AspNetUsers_UserId;  
    ALTER TABLE app.user_tokens CHECK CONSTRAINT FK_AspNetUserTokens_AspNetUsers_UserId;  

commit transaction