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

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:

  1. 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.').

  1. 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.