Generating Synergy App Users Logins (or self service)

use Horio_UK_Tclarke

--- *** Replace the Database Name ***

--automatically create users from employees --should loop throug users without employees --create users --assign created user to security group SYQR

declare @id int, @code nvarchar(max), @firstName nvarchar(max), @lastName nvarchar(max) declare employee_cursor cursor local fast_forward for select Id, Code, FirstName, LastName from Employees where Code not in (select [Login] from [User]) open employee_cursor while 0=0 begin fetch next from employee_cursor into @id, @code, @firstName, @lastName if (@@FETCH_STATUS <> 0) BREAK if not exists (select * from [User] where [Login] = @code or Email = @firstname+ '.' + @lastname + '@tclarke.co.uk')--- *** update email domain *** begin --create user insert into [User] (FirstName, LastName, Email, [Login], [Password], CreateDate, ShouldChangePasswordNextTime, PasswordNeverExpires, ChangePasswordPeriod, IsLockedOut) values(@firstName, @lastName, @firstname+ '.' + @lastname + '@tclarke.co.uk', --- *** update email domain *** @code, '$2a$10$.qIw3usekMuRPFQnclz39uhmayO4zsG3/Mj6w32A4CAs9DwKtzzvC', --default password is Pa55w@rd GETDATE(), 0, --only 0 or 1 allowed 1, --only 0 or 1 allowed null, --set NULL instead of empty 0) --only 0 or 1 allowed --set software access role insert into UsersInRoles (UserID, RoleID, IsActive) values((select top 1 Id from [User] where [Login] = @code), (select top 1 Id from [Role] where [Name] = 'SynergyApp'), *** Update the description of the software security description Name= *** 1) end --map user with employee and set to give access to SYQR update Employees set UserId = (select top 1 Id from [User] where [Login] = @code), IsHaveQrApplication = 1 where Id = @id end close employee_cursor deallocate employee_cursor

---This only works if all users have the same email format & domain