Automation Hub Tenant Account Owner Change Guide For On-Premises (Via Automation Suite)

system · August 8, 2023, 8:52am

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:

<old_user_email>: This is the email address of the current account owner.
<new_user_email>: This is the email address of the new account owner.
<new_user_first_name>: This is the first name of the new account owner.
<new_user_last_name>: This is the last name of the new account owner.
<user input>: 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 your 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 '<old_user_email>' with the actual email.
-- @NewUserEmail: This is the email address of the new owner of the account. Replace '<new_user_email>' with the actual email.
-- @NewUserFirstName: The first name of the new owner. Replace '<new_user_first_name>' with the actual first name.
-- @NewUserLastName: The last name of the new owner. Replace '<new_user_last_name>' with the actual last name.
-- @OrgName: This is the name of the Automation Suite organization. Replace '<user input>' with the actual tenant name.
-- @TenantName: This is the name of the Automation Suite tenant. Replace '<user input>' with the actual tenant name.
-- @FdTablePrefix: This is the prefix of the relevant tables, often taking the form 'ah.'. Replace '<user input>' 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) = '<old_user_email>';
DECLARE @NewUserEmail nvarchar(255) = '<new_user_email>';
DECLARE @NewUserFirstName nvarchar(255) = '<new_user_first_name>';
DECLARE @NewUserLastName nvarchar(255) = '<new_user_last_name>';
DECLARE @OrgName nvarchar(255) = '<user input>';
DECLARE @TenantName nvarchar(255) = '<user input>';
DECLARE @FdTablePrefix nvarchar(255) = '<user input>';

-- DO NOT EDIT ANYTHING BEYOND THIS POINT -- 

BEGIN TRY
    BEGIN TRANSACTION;

    -- Validate placeholders
    IF  @OldUserEmail = '<old_user_email>' OR 
        @NewUserEmail = '<new_user_email>' OR 
        @NewUserFirstName = '<new_user_first_name>' OR 
        @NewUserLastName = '<new_user_last_name>' OR 
        @OrgName = '<user input>' OR 
        @TenantName = '<user input>' OR 
        @FdTablePrefix = '<user input>'
    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

Additional 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 us to restore the database to its previous state in case of any issues.

Most Active Users - Yesterday
mpraveen1902
Palaniyappan
Parvathy
Anil_G
Swathi_Raju
Sara_Piana
Yoichi
Nitya1
ashwin.ashok
Rishita_aggarwal
More details...