{"id":1065,"date":"2016-01-15T01:28:44","date_gmt":"2016-01-15T01:28:44","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1065"},"modified":"2017-03-25T16:32:01","modified_gmt":"2017-03-25T16:32:01","slug":"generate-ddl-for-a-role-in-a-teradata-database","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1065","title":{"rendered":"Generate DDL for a Role in a Teradata Database"},"content":{"rendered":"<p>There are times I would like to copy the permissions for a role with a Teradata database to use on another database.\u00a0 Below is a nice query to give me the DDL.<\/p>\n<p><strong><span style=\"color: #ff0000;\">Note: I am not the author of this query, just re-posting it:<\/span><\/strong><\/p>\n<pre>SELECT\r\nx.ROLENAME,\r\nx.AccessRight,\r\nx.DATABASENAME,\r\nx.TABLENAME,\r\n'GRANT '|| CASE\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'AE'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'ALTER EXTERNAL PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'AF'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'ALTER FUNCTION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'AP'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'ALTER PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'AR'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE RESTORE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'AS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'ABORT SESSION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CA'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE AUTHORIZATION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE DATABASE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CE'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE EXTERNAL PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CF'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE FUNCTION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CG'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE TRIGGER'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CM'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE MACRO'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CO'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE PROFILE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CP'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CHECKPOINT'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CR'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE ROLE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CT'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE TABLE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CU'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE USER'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'CV'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE VIEW'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'D'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DELETE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DA'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP AUTHORIZATION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP DATABASE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DF'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP FUNCTION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DG'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP TRIGGER'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DM'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP MACRO'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DO'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP PROFILE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DP'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DUMP'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DR'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP ROLE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DT'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP TABLE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DU'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP USER'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'DV'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP VIEW'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'E'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'EXECUTE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'EF'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'EXECUTE FUNCTION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'GC'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE GLOP'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'GD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP GLOP'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'GM'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'GLOP MEMBER'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'I'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'INSERT'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'IX'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'INDEX'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'MR'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'MONITOR RESOURCE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'MS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'MONITOR SESSION'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'NT'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'NONTEMPORAL'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OA'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE DUMP'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE DELETE POLICY'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OI'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE INSERT POLICY'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OP'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE OWNER PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE SELECT POLICY'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'OU'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'OVERRIDE UPDATE POLICY'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'PC'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CREATE PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'PD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'DROP PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'PE'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'EXECUTE PROCEDURE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'R'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SELECT'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'RF'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'REFERENCE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'RO'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'REPLCONTROL'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'RS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'RESTORE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SA'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SECURITY CONSTRAINT ASSIGNMENT (system wide)'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SD'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SECURITY CONSTRAINT DEFINITION (system wide)'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SH'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SHOW'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SR'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SET RESOURCE RATE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'SET SESSION RATE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'ST'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'STATISTICS'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'TH'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'CTCONTROL'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'U'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'UPDATE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'UM'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'UDT METHOD'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'UT'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'UDT TYPE'\r\nWHEN ACCESSRIGHT =\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'UU'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\u00a0\u00a0 'UDT USAGE'\r\nEND || ' ON '||TRIM(x.databasename)||' to '||TRIM(x.ROLENAME)||';' AS Permission\r\nFROM \r\n(SELECT distinct A.ROLENAME as ROLENAME,\r\n\u00a0\u00a0\u00a0 B.DATABASENAME as DATABASENAME,\r\n\u00a0\u00a0\u00a0 B.TABLENAME as TABLENAME,\r\n\u00a0\u00a0\u00a0 B.GRANTORNAME as GRANTORNAME,\r\n\u00a0\u00a0\u00a0 B.AccessRight as AccessRight\r\nFROM\u00a0\u00a0\u00a0 DBC.ROLEMEMBERS A\r\nJOIN\u00a0\u00a0\u00a0 DBC.ALLROLERIGHTS B\r\nON A.ROLENAME = B.ROLENAME\r\nWHERE DATABASENAME =' &lt; database_name &gt; '\u00a0\u00a0 --- database name between quotes\r\nGROUP BY 1,2,3,4,5) as x\r\norder by 1,2<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2016\/01\/ExtractDDLforRoles.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1066 size-large\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2016\/01\/ExtractDDLforRoles-1024x613.jpg\" alt=\"ExtractDDLforRoles\" width=\"642\" height=\"384\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2016\/01\/ExtractDDLforRoles-1024x613.jpg 1024w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2016\/01\/ExtractDDLforRoles-300x179.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2016\/01\/ExtractDDLforRoles.jpg 1109w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/a><\/p>\n<p>Final Note: If you have an improvement on this query, I would be happy to post it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are times I would like to copy the permissions for a role with a Teradata database to use on another database.\u00a0 Below is a nice query to give me &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=1065\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1066,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54],"tags":[60,59,55],"class_list":["post-1065","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teradata","tag-ddl","tag-role","tag-sql-assistant"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1065"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1065"}],"version-history":[{"count":7,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1065\/revisions"}],"predecessor-version":[{"id":1371,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1065\/revisions\/1371"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1066"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}