List users in Teradata database

Query below returns list of users excluding system ones in Teradata database with their roles.

Query

SELECT  UserName,
        CreatorName,
        DefaultDatabase,
        CreateTimeStamp,
        TRIM(TRAILING ',' FROM
            XMLAGG(R.RoleName || ',')(VARCHAR(255))) AS Roles
FROM DBC.UsersV U
LEFT JOIN DBC.RoleMembersV R
ON  U.Username = R.Grantee
WHERE   U.Username NOT IN ('TDPUSER', 'Crashdumps', 'tdwm', 'DBC',
        'LockLogShredder', 'TDMaps', 'Sys_Calendar', 'SysAdmin', 
        'SystemFe', 'External_AP')
GROUP BY    UserName,
            CreatorName,
            DefaultDatabase,
            CreateTimeStamp
ORDER BY U.Username;

Columns

  • UserName - name of the user
  • CreatorName - name of the user who created user
  • DefaultDatabase - user's default database
  • CreateTimeStamp - time when user was created
  • Roles - granted roles to user

Rows

  • One row represents one user in the database
  • Scope of rows: non-system users in the database
  • Ordered by username

Sample results

sample results

0
There are no comments. Click here to write the first comment.