Duplicate Teradata Users !!

Teradata Administrator (WinDDI) gives us an option to clone the users. This will create a new user with the same owner, profile, role, perm, spool, temp and fallback option. But it does not copy the privilages the user has (atleast in V2R5). The below script will generate a list of grant statements that can be executed to replicate the privilages.

PS : Just change the SourceUserName and TargetUserName in the script.

/***************************************************/
/* Script to copy User Privilages */
/***************************************************/


.SET DEFAULTS
.SET WIDTH 254
.SET QUIET ON
.SET ECHOREQ ON

.OS IF EXIST CopyUser.out DEL CopyUser.out

.EXPORT REPORT FILE=CopyUser.out

SELECT
'GRANT '
|| CASE
WHEN ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
WHEN ACCESSRIGHT = 'AS' THEN 'ABORT SESSION'
WHEN ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
WHEN ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
WHEN ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
WHEN ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE'
WHEN ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
WHEN ACCESSRIGHT = 'CR' THEN 'CREATE ROLE'
WHEN ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
WHEN ACCESSRIGHT = 'CU' THEN 'CREATE USER'
WHEN ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
WHEN ACCESSRIGHT = 'D' THEN 'DELETE'
WHEN ACCESSRIGHT = 'DD' THEN 'DROP DATABASE'
WHEN ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER'
WHEN ACCESSRIGHT = 'DM' THEN 'DROP MACRO'
WHEN ACCESSRIGHT = 'DO' THEN 'DROP PROFILE'
WHEN ACCESSRIGHT = 'DP' THEN 'DUMP'
WHEN ACCESSRIGHT = 'DR' THEN 'DROP ROLE'
WHEN ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
WHEN ACCESSRIGHT = 'DU' THEN 'DROP USER'
WHEN ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
WHEN ACCESSRIGHT = 'E' THEN 'EXECUTE'
WHEN ACCESSRIGHT = 'I' THEN 'INSERT'
WHEN ACCESSRIGHT = 'IX' THEN 'INDEX'
WHEN ACCESSRIGHT = 'MR' THEN 'MONITOR RESOURCE'
WHEN ACCESSRIGHT = 'MS' THEN 'MONITOR SESSION'
WHEN ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
WHEN ACCESSRIGHT = 'RO' THEN 'REPLICATION OVERRIDE'
WHEN ACCESSRIGHT = 'R'THEN 'SELECT'
WHEN ACCESSRIGHT = 'RF' THEN 'REFERENCE'
WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'
WHEN ACCESSRIGHT = 'SS' THEN 'SET SESSION RATE'
WHEN ACCESSRIGHT = 'SR' THEN 'SET RESOURCE RATE'
WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'
END
|| ' ON '
|| TRIM(DATABASENAME)
|| ' TO TargetUserName '
|| CASE WHEN GRANTAUTHORITY = 'Y' THEN ' WITH GRANT OPTION;' ELSE ' ;' END
FROM DBC.ALLRIGHTS
WHERE USERNAME = SourceUserName
GROUP BY 1
;


.EXPORT RESET

/* End of Script */