If the Automation Hub account owner has left or needs to be changed, how to perform this change ?
Prerequisites: The new account owner should already be a user in the Automation Hub tenant -> users table.
Note: This script should be run in the AutomationSuite_Automation_Hub database. This ensures the correct database context for the operations performed by the script.
Step-by-step Guide:
- Replace placeholders in the script with the actual data. Replace the following placeholders:
- : This is the email address of the current account owner.
- : This is the email address of the new account owner.
- : This is the first name of the new account owner.
- : This is the last name of the new account owner.
- : Replace these with the actual tenant name, organization name and the prefix of the relevant tables (often taking the form 'ah.').
- Execute the script:
With all the actual data filled in and the SQL Management Studio connected to the correct database, it should be possible now to execute the script. It is a transactional script - if any errors occur, it will abort and print out the error details for debugging. After successfully running, it will print: 'Script execution completed successfully.'.
The script performs the following actions,
- Validates if all placeholders have been replaced
- Retrieves the tenant_uuid and tenant_db_table_prefix from relevant tables
- Gets the user_id of the new account owner from users table
- Checks if the provided old user email is the current account owner
- Checks if the new user is already the account owner
- Adds the new user to the tenant_account_owners table and retrieves or sets the account_owner_id
- Updates the tenant's table with the new owner's id
- Removes the old user's ah-account-owner role
- Assigns the ah-account-owner role to the new user
- Updates the tenant_data table's admin_user_id to the new user_id.
-- IMPORTANT: -- This script should be run in the AutomationSuite_Automation_Hub database. -- This script is designed to change the account owner in the Automation Hub. Please replace the placeholders with actual data before running the script. -- Assumptions: -- The new account owner is already a user in the Automation Hub tenant -> users table. -- Variables: -- @OldUserEmail: This is the email address of the current owner of the account. Replace '' with the actual email. -- @NewUserEmail: This is the email address of the new owner of the account. Replace '' with the actual email. -- @NewUserFirstName: The first name of the new owner. Replace '' with the actual first name. -- @NewUserLastName: The last name of the new owner. Replace '' with the actual last name. -- @OrgName: This is the name of the Automation Suite organization. Replace '' with the actual tenant name. -- @TenantName: This is the name of the Automation Suite tenant. Replace '' with the actual tenant name. -- @FdTablePrefix: This is the prefix of the relevant tables, often taking the form 'ah.'. Replace '' with the actual prefix you're working with. -- Please note that the script will abort if it encounters any errors, and it will print out the error details for debugging. SET XACT_ABORT ON; -- Replace the placeholders with actual data DECLARE @OldUserEmail nvarchar(255) = ''; DECLARE @NewUserEmail nvarchar(255) = ''; DECLARE @NewUserFirstName nvarchar(255) = ''; DECLARE @NewUserLastName nvarchar(255) = ''; DECLARE @OrgName nvarchar(255) = ''; DECLARE @TenantName nvarchar(255) = ''; DECLARE @FdTablePrefix nvarchar(255) = ''; -- DO NOT EDIT ANYTHING BEYOND THIS POINT -- BEGIN TRY BEGIN TRANSACTION; -- Validate placeholders IF @OldUserEmail = '' OR @NewUserEmail = '' OR @NewUserFirstName = '' OR @NewUserLastName = '' OR @OrgName = '' OR @TenantName = '' OR @FdTablePrefix = '' BEGIN RAISERROR('One or more placeholders were not replaced with actual data. Please replace all placeholders and try again.', 16, 1); RETURN; END -- Declaring variables for placeholders DECLARE @NewOwnerID bigint; DECLARE @NewUserID bigint; DECLARE @AccountOwnerRoleID bigint; DECLARE @TenantUUID nvarchar(255); DECLARE @TsTablePrefix nvarchar(255); DECLARE @CurrentOwnerEmail nvarchar(255); -- Prepare SQL statement DECLARE @sql nvarchar(max); DECLARE @params nvarchar(max); -- Get the tenant_uuid from cloud_tenants table based on the TenantName And Org Name SET @sql = N'SELECT @TenantUUID = tenant_uuid FROM ' + @FdTablePrefix + 'cloud_tenants WHERE cloud_TenantName = @TenantName AND cloud_AccountName = @OrgName;'; SET @params = N'@FdTablePrefix nvarchar(255), @TenantName nvarchar(255), @OrgName nvarchar(255), @TenantUUID nvarchar(255) OUTPUT'; EXEC sp_executesql @sql, @params, @FdTablePrefix = @FdTablePrefix, @TenantName = @TenantName, @OrgName = @OrgName, @TenantUUID = @TenantUUID OUTPUT; -- If the tenant_uuid was not found, abort the script IF (@TenantUUID IS NULL) BEGIN RAISERROR('No tenant found with the provided TenantName and OrgName. Aborting script.', 16, 1); RETURN; END -- Get the tenant_db_table_prefix from tenants table based on the tenant_uuid SET @sql = N'SELECT @TsTablePrefix = tenant_db_table_prefix FROM ' + @FdTablePrefix + 'tenants WHERE tenant_uuid = @TenantUUID;'; SET @params = N'@FdTablePrefix nvarchar(255), @TenantUUID nvarchar(255), @TsTablePrefix nvarchar(255) OUTPUT'; EXEC sp_executesql @sql, @params, @FdTablePrefix = @FdTablePrefix, @TenantUUID = @TenantUUID, @TsTablePrefix = @TsTablePrefix OUTPUT; -- If the tenant_db_table_prefix was not found, abort the script IF (@TsTablePrefix IS NULL) BEGIN RAISERROR('No tenant found in the tenants table with the retrieved tenant_uuid. Aborting script.', 16, 1); RETURN; END -- Check if the old user email is the current account owner SET @sql = N'SELECT @CurrentOwnerEmail = u.user_email FROM ' + @TsTablePrefix + 'users u INNER JOIN ' + @TsTablePrefix + 'ah_user_roles ur ON ur.user_id = ur.user_id INNER JOIN ' + @TsTablePrefix + 'ah_roles r ON ur.role_id = r.role_id WHERE r.role_slug = ''ah-account-owner'''; SET @params = N'@TsTablePrefix nvarchar(255), @CurrentOwnerEmail nvarchar(255) OUTPUT'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @CurrentOwnerEmail = @CurrentOwnerEmail OUTPUT; -- If the old user email is not the current account owner, abort the script IF (@CurrentOwnerEmail != @OldUserEmail) BEGIN RAISERROR('The provided old user email is not the current account owner. Aborting script.', 16, 1); RETURN; END -- Get the user_id of the new account owner from users table SET @sql = N'SELECT @NewUserID = user_id FROM ' + @TsTablePrefix + 'users WHERE user_email = @NewUserEmail;'; SET @params = N'@FdTablePrefix nvarchar(255), @NewUserEmail nvarchar(255), @NewUserID bigint OUTPUT'; EXEC sp_executesql @sql, @params, @FdTablePrefix = @FdTablePrefix, @NewUserEmail = @NewUserEmail, @NewUserID = @NewUserID OUTPUT; -- If the new user_id was not found, abort the script IF (@NewUserID IS NULL) BEGIN RAISERROR('No user found with the provided new user email. Aborting script.', 16, 1); RETURN; END -- Check if the new user is already the account owner SET @sql = N'SELECT @AccountOwnerRoleID = ur.user_id FROM ' + @TsTablePrefix + 'ah_user_roles ur INNER JOIN ' + @TsTablePrefix + 'ah_roles r ON ur.role_id = r.role_id WHERE r.role_slug = ''ah-account-owner'' AND ur.user_id = @NewUserID'; SET @params = N'@TsTablePrefix nvarchar(255), @NewUserID bigint, @AccountOwnerRoleID int OUTPUT'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @NewUserID = @NewUserID, @AccountOwnerRoleID = @AccountOwnerRoleID OUTPUT; IF (@AccountOwnerRoleID IS NOT NULL) BEGIN PRINT 'New user is already the account owner. No changes required...'; RETURN; END PRINT 'Starting Step 1...' -- STEP 1: Add the new user to the tenant_account_owners table if they don't already exist SET @sql = N'IF NOT EXISTS (SELECT * FROM ' + @FdTablePrefix + 'tenant_account_owners WHERE user_email = @NewUserEmail) BEGIN PRINT ''Inserting new user into tenant_account_owners table...''; INSERT INTO ' + @FdTablePrefix + 'tenant_account_owners(user_first_name, user_last_name, user_email, created_epoch) VALUES (@NewUserFirstName, @NewUserLastName, @NewUserEmail, DATEDIFF_BIG(SECOND, ''1970-01-01'', GETUTCDATE())); SET @NewOwnerID = SCOPE_IDENTITY(); PRINT ''New user inserted with account_owner_id: '' + CAST(@NewOwnerID AS nvarchar(255)); END ELSE BEGIN SET @NewOwnerID = (SELECT account_owner_id FROM ' + @FdTablePrefix + 'tenant_account_owners WHERE user_email = @NewUserEmail); PRINT ''User already exists in tenant_account_owners table with account_owner_id: '' + CAST(@NewOwnerID AS nvarchar(255)); END'; SET @params = N'@FdTablePrefix nvarchar(255), @NewUserEmail nvarchar(255), @NewUserFirstName nvarchar(255), @NewUserLastName nvarchar(255), @NewOwnerID bigint OUTPUT'; EXEC sp_executesql @sql, @params, @FdTablePrefix = @FdTablePrefix, @NewUserFirstName = @NewUserFirstName, @NewUserLastName = @NewUserLastName, @NewUserEmail = @NewUserEmail, @NewOwnerID = @NewOwnerID OUTPUT; PRINT 'Step 1 completed...' PRINT 'Starting Step 2...' -- STEP 2: Update the tenants table with the new owner's id SET @sql = N'UPDATE ' + @FdTablePrefix + 'tenants SET account_owner_id = @NewOwnerID WHERE tenant_uuid = @TenantUUID'; SET @params = N'@FdTablePrefix nvarchar(255), @TenantUUID nvarchar(255), @NewOwnerID bigint'; EXEC sp_executesql @sql, @params, @FdTablePrefix = @FdTablePrefix, @NewOwnerID = @NewOwnerID, @TenantUUID = @TenantUUID; PRINT 'Tenants table has been updated with new account owner id...'; PRINT 'Step 2 completed...' PRINT 'Starting Step 3...' -- STEP 3: Remove the old user's 'ah-account-owner' role SET @sql = N'DELETE FROM ' + @TsTablePrefix + 'ah_user_roles WHERE user_id = (SELECT user_id FROM ' + @TsTablePrefix + 'users WHERE user_email = @OldUserEmail) AND role_id = (SELECT role_id FROM ' + @TsTablePrefix + 'ah_roles WHERE role_slug = ''ah-account-owner'')'; SET @params = N'@TsTablePrefix nvarchar(255), @OldUserEmail nvarchar(255)'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @OldUserEmail = @OldUserEmail; PRINT 'Old user''s ''ah-account-owner'' role has been removed from ah_user_roles table...'; PRINT 'Step 3 completed...' PRINT 'Starting Step 4...' -- STEP 4: Assign 'ah-account-owner' role to the new user SET @sql = N'SELECT @AccountOwnerRoleID = role_id FROM ' + @TsTablePrefix + 'ah_roles WHERE role_slug = ''ah-account-owner'''; SET @params = N'@TsTablePrefix nvarchar(255), @AccountOwnerRoleID int OUTPUT'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @AccountOwnerRoleID = @AccountOwnerRoleID OUTPUT; SET @sql = N'IF NOT EXISTS (SELECT * FROM ' + @TsTablePrefix + 'ah_user_roles WHERE user_id = @NewUserID AND role_id = @AccountOwnerRoleID) BEGIN INSERT INTO ' + @TsTablePrefix + 'ah_user_roles(user_id, role_id, category_id, created_epoch, created_at) VALUES (@NewUserID, @AccountOwnerRoleID, 0, DATEDIFF_BIG(SECOND, ''1970-01-01'', GETUTCDATE()), GETDATE()); END'; SET @params = N'@TsTablePrefix nvarchar(255), @NewUserID bigint, @AccountOwnerRoleID int'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @NewUserID = @NewUserID, @AccountOwnerRoleID = @AccountOwnerRoleID; PRINT 'New user has been given account owner privileges...'; PRINT 'Step 4 completed...' PRINT 'Starting Step 5...' -- STEP 5: Update the tenants_data table admin_user_id to new user_id SET @sql = N'UPDATE ' + @TsTablePrefix + 'tenant_data SET admin_user_id = @NewUserID WHERE tenant_connection_uuid = @TenantUUID'; SET @params = N'@TsTablePrefix nvarchar(255), @NewUserID bigint, @TenantUUID nvarchar(255)'; EXEC sp_executesql @sql, @params, @TsTablePrefix = @TsTablePrefix, @NewUserID = @NewUserID, @TenantUUID = @TenantUUID; PRINT 'Tenants_data table has been updated with the new admin user id...'; PRINT 'Step 5 completed...' COMMIT TRANSACTION; PRINT 'Script execution completed successfully.'; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; -- Output error information PRINT 'Error occurred during script execution:'; PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)); PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), ''); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)); PRINT 'Error Message: ' + ERROR_MESSAGE(); END CATCH
Note: Ensure to replace all placeholders in the script before running it.
Also,
- This guide assumes that the new account owner is already added as a user in the Automation Hub tenant -> users table. If not, add them before starting the process
- Remember to take a backup of the database before making any changes. This will allow to restore the database to its previous state in case of any issues.