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.