· 6 years ago · Oct 14, 2019, 01:18 PM
1IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#TempUsers2')) DROP TABLE #TempUsers2
2GO
3CREATE TABLE #TempUsers2 (UserName nvarchar(200), DisplayName nvarchar(200), FirstName nvarchar(200), LastName nvarchar(200), ForcePasswordChange BIT, ExternalUserId nvarchar(128))
4GO
5
6-- to give yourself ALL roles in agilab/localhost, add you user name to the list below
7INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Claire.Furney@agilisys.co.uk', 'Claire Furney','Claire','Furney', 0, 'b783a849-fa75-4b44-8b7a-e03eca97ecd9')
8INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Daniel.Britton@agilisys.co.uk', 'Daniel Britton','Daniel','Britton', 0, NULL)
9INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Lalit.Nankani@agilisys.co.uk', 'Lalit Nankani','Lalit','Nankani', 0, '34da9a7b-e779-4acc-aa5e-648e77021d97')
10INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Mehul.Gurjar@agilisys.co.uk', 'Mehul Gurjar','Mehul','Gurjar', 0, '802f52df-3859-4bc5-9286-bfa65a6d9280')
11INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Sam.Mugford@agilisys.co.uk', 'Sam Mugford','Sam','Mugford', 0, 'e6be6f20-6668-45a3-a8ab-36eeccdb58df')
12INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Sam.Mugford@agd.io', 'Sam.Mugford@agd.io','Same','Mugforde', 0, 'f9481827-d30a-422d-8b62-76de902d218b')
13INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Sumithra.Krishnamurthy@agilisys.co.uk', 'Sumithra Krishnamurthy','Sumithra','Krishnamurthy', 0, 'aa8fbe42-a15a-41c0-bb7e-cd32938307f9')
14INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('Zia.UdDin@agilisys.co.uk', 'Zia Ud Din','Zia Ud Din','Zia Ud Din', 0, '941c86dc-fad4-4c08-9c79-cde108457707')
15INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('zia.uddin@agd.io', 'zia.uddin@agd.io','Zia', 'Ud Din', 0, '348c2816-fa5e-4eb4-9e36-0b0d11f3b3e0')
16INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('MABO.Test@agilab.local', 'MABO Test','MABO', 'Test', 0, NULL)
17INSERT INTO #TempUsers2 (UserName, DisplayName, FirstName, LastName,ForcePasswordChange, ExternalUserId) VALUES ('sachin.vishwakarma@agilisys.co.uk', 'MABO Test','MABO', 'Test', 0, NULL)
18GO
19
20DECLARE @intFlag INT
21SELECT @intFlag = COUNT (DISTINCT [UserName])FROM #TempUsers2
22PRINT @intFlag
23DECLARE @MyAccountApplicationId uniqueidentifier
24DECLARE @ADContactApplicationId uniqueidentifier
25DECLARE @Password NVARCHAR(255)
26DECLARE @PasswordSalt NVARCHAR(255)
27DECLARE @UserId uniqueidentifier
28DECLARE @SecretQuestion NVARCHAR(255)
29DECLARE @SecretAnswer NVARCHAR(255)
30
31SET @MyAccountApplicationId = 'FF360009-D487-4946-8FF9-D9506C85543B'
32SET @ADContactApplicationId = 'A02D552C-DD13-4478-82AF-2377C66ED220'
33SET @Password = N'3O1ImXyzOc0pPya/4jbDSfNrQe1+LfA1M4/Ubl+PtyDij9sURStEPeatzinvrxzV' -- the standard password
34SET @PasswordSalt = N'GG89h6Qu9RECWkWIUTgzRQ=='
35SET @SecretQuestion = N'What is the name of your first school?'
36SET @SecretAnswer = N'cJ8652+qq597h30KcM1SyHy9EXvr3ypU+zUhO6NZJaI='
37
38DECLARE @currentUserEmail NVARCHAR(200)
39DECLARE @currentUserDisplayName NVARCHAR(200)
40DECLARE @currentUserFirstName NVARCHAR(200)
41DECLARE @currentUserLastname NVARCHAR(200)
42DECLARE @currentUserForcePasswordChange BIT
43DECLARE @currentUserExternalUserId NVARCHAR(128)
44
45PRINT 'Iterating through list of users'
46WHILE (@intFlag > 0)
47BEGIN
48SELECT @currentUserEmail = (SELECT TOP 1 UserName FROM #TempUsers2 ORDER BY UserName ASC)
49SELECT @currentUserDisplayName = (SELECT TOP 1 DisplayName FROM #TempUsers2 ORDER BY UserName ASC)
50SELECT @currentUserFirstName = (SELECT TOP 1 FirstName FROM #TempUsers2 ORDER BY UserName ASC)
51SELECT @currentUserLastname = (SELECT TOP 1 LastName FROM #TempUsers2 ORDER BY UserName ASC)
52SELECT @currentUserForcePasswordChange = (SELECT TOP 1 ForcePasswordChange FROM #TempUsers2 ORDER BY UserName ASC)
53SELECT @currentUserExternalUserId = (SELECT TOP 1 ExternalUserId FROM #TempUsers2 ORDER BY UserName ASC)
54PRINT 'CurrentUser Email: ' + @currentUserEmail
55PRINT 'CurrentUser ExternalUserId: ' + @currentUserExternalUserId
56
57
58-- Step 1. create the user
59SET @UserId = NEWID()
60PRINT 'New UserId: ' + convert(nvarchar(50),@UserId)
61IF NOT EXISTS (SELECT 1 FROM [dbo].[aspnet_Users] WHERE [UserName] = @currentUserEmail AND [ApplicationId] = @ADContactApplicationId)
62INSERT INTO [dbo].[aspnet_Users] ([UserId], [ApplicationId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate])
63 VALUES (@UserId, @ADContactApplicationId, @currentUserEmail, @currentUserEmail, NULL, 0, GETDATE())
64
65SET @UserId = (SELECT [UserId] FROM [dbo].[aspnet_Users] WHERE [UserName] = @currentUserEmail AND [ApplicationId] = @ADContactApplicationId)
66PRINT 'Current UserId: ' + convert(nvarchar(50),@UserId)
67
68IF NOT EXISTS (SELECT 1 FROM [dbo].[aspnet_Membership] WHERE [Email] = @currentUserEmail AND [ApplicationId] = @ADContactApplicationId)
69INSERT INTO [dbo].[aspnet_Membership] ([UserId], [ApplicationId], [Password], [PasswordFormat], [PasswordSalt], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate],[LastLoginDate],[LastPasswordChangedDate],[LastLockoutDate],[FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowStart])
70 VALUES (@UserId, @ADContactApplicationId, @Password,2, @PasswordSalt, @currentUserEmail,@currentUserEmail,@SecretQuestion,@SecretAnswer, 1,0, GETDATE(), GETDATE(),GETDATE(),GETDATE(),0,'1754-01-01',0,'1754-01-01')
71
72
73IF NOT EXISTS (SELECT 1 FROM [dbo].[AgTbl_CouncilUserProfile] WHERE [UserId] = @UserId)
74INSERT INTO [dbo].[AgTbl_CouncilUserProfile] ([UserId],[FirstName],[LastName],[CouncilUserAlias],[DateOfBirth],[IsEnable],[ForcePasswordChange],[LoginRedirectUrl])
75 VALUES (@UserId, @currentUserFirstName, @currentUserLastname, @currentUserDisplayName, NULL,1,@currentUserForcePasswordChange,NULL)
76
77 -- Step 2. add Administrator role to user
78IF NOT EXISTS (SELECT 1 FROM [dbo].[AgTbl_UserInRoles2] WHERE [RoleCode] = 'Administrator' AND [UserId] = @UserId)
79INSERT INTO [dbo].[AgTbl_UserInRoles2]
80 ([RoleCode]
81 ,[UserId]
82 ,[CreatedBy]
83 ,[CreatedOn]
84 ,[LastUpdatedBy]
85 ,[LastUpdatedOn])
86 VALUES
87 ('Administrator'
88 ,@UserId
89 ,'Install'
90 ,GETDATE()
91 ,'Install'
92 ,GETDATE())
93
94IF (@currentUserExternalUserId <> '') AND (NOT EXISTS (SELECT 1 FROM [dbo].[AgTbl_ExternalUserLogins] WHERE [UserId] = @UserId AND [LoginProvider] = 'AzureAD'))
95BEGIN
96PRINT 'Inserting External User Login into [AgTbl_ExternalUserLogins] for Azure AD for user: ' + @currentUserEmail
97INSERT INTO [dbo].[AgTbl_ExternalUserLogins]
98 ([LoginProvider]
99 ,[ProviderKey]
100 ,[UserId])
101 VALUES
102 ('AzureAD'
103 ,@currentUserExternalUserId
104 ,@UserId)
105END
106
107PRINT 'Deleting user from temp table: ' + @currentUserEmail
108DELETE FROM #TempUsers2 WHERE UserName = @currentUserEmail
109SET @intFlag = @intFlag - 1
110END
111GO