List users in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept