Sample ABL recreate SQL DBA users in an empty database

Sample ABL recreate SQL DBA users in an empty database.

Example of ABL code to generate the SQL create and SQL grant statements needed to recreate the dba users of an existing database on a new database having no users defined.

Option #1 - recreate DBA users in an empty database with no entries in, or plans to imports previous users to, the _User table.

Execute this ABL code against the existing database where the users with DBA privileges need to be copied from:

DEFINE VARIABLE cPassword AS CHARACTER FORMAT "x(20)" LABEL "Password" NO-UNDO.

FOR EACH _User NO-LOCK:
    FOR EACH _Sysdbauth WHERE _Sysdbauth._grantee = _User._Userid NO-LOCK:
        
        /* Provide password in plain text for the script. */
        /* _Grantee is shown to identify the user in question. */
        /* Only update the _Grantee field if you want to use a different username. */

        UPDATE cPassword _GRANTEE.

        OUTPUT TO "createDbaUsers.sql" APPEND.
            PUT UNFORMATTED "CREATE USER '" + _GRANTEE + "' , '" + cPassword + "';" SKIP.
            PUT UNFORMATTED "GRANT DBA TO '" + _GRANTEE + "'; " SKIP.
            PUT UNFORMATTED "COMMIT WORK;" SKIP.
        OUTPUT CLOSE.
    END.
END.

The produced file createDbaUsers.sql can then be applied on a new database having no users defined with the command:

sqlexp -db databaseName -S 5555 -char -infile createDbaUsers.sql -user <databaseCreator>

Command to be executed by the same operating system user that created the database <databaseName>.

Option #2 - re-grant DBA privileges to existing users in a new database.

Execute this ABL code against the existing database where the users with DBA privileges need to be copied from:
 

OUTPUT TO VALUE("createDbaUsers.sql") APPEND.
FOR EACH _User NO-LOCK:
    FOR EACH _Sysdbauth WHERE _Sysdbauth._grantee = _User._Userid NO-LOCK:
        PUT UNFORMATTED "GRANT DBA TO " + _GRANTEE + "; " SKIP.
        PUT UNFORMATTED "COMMIT WORK;" SKIP.
    END.
END.
OUTPUT CLOSE.

The resulting file grants DBA privileges to existing users.
In order to apply it, enable the sysprogress user and execute it:

sqlexp -db databaseName -S 5555 -char -infile createDbaUsers.sql -user sysprogress -password sysprogress

References to other Documentation:

Progress article(s):

Basic Guide to Defining Progress SQL-92 Database Permissions & Security