Skip to Content
Technical Articles
Author's profile photo Mark Mumy

Copying a Login From One SAP IQ or SAP HDLRE Instance to Another

Over the years, many customers have asked for the ability to copy or move logins from one SAP IQ instance to another as part of the normal development progression.  Times arise when you want to maintain the same password.  Fortunately, there are methods to do this.  What I will describe below is the same process that SAP uses as part of the iqunload utility that is shipped with SAP IQ and the SAP HANA Cloud, data lake relational engine (HDLRE).


In order to copy a login and password, we must use two system table: SYS.SYSYSER and SYS.SYSUSERPASSWORD.  These tables contain the login as well as password, if set, for each user and group in the system.


The process to extract the password is fairly easy.  We simply need the USER_NAME and the PASSWORD columns.  The password is encrypted using a secure method from SAP.  We will not be able to reverse engineer the password.  However, the CREATE USER and GRANT CONNECT syntax allow us to pass the encrypted password.  This way, the user can be added to the new system with the password from the old system, should you choose to do that.


The process outlined below pulls out every user and non-null password from the system, you can specify a username or wildcard, and generated the proper GRANT CONNECT statement.  As part of this process, however, the encrypted password must be converted to a hexidecimal string.  I’ve included the code for the function hextostr to do this.


The code looks like this:

drop function if exists dbo.hextostr;
drop procedure if exists dbo.sp_iqreverse_passwd;

create function dbo.hextostr ( bin_expr varbinary(255) )
        returns varchar(255)
        declare data_len int;
        declare i int;
        declare newstr varchar(255);
        -- for IQ v15
        set temporary option ansi_substring='off';

        set data_len = datalength( bin_expr );
        set newstr = '';
        set i = 1;
        while i <= data_len loop
            set newstr = newstr || '\x' || lower( substring ( inttohex ( substring( bin_expr,i,1) ), 15, 2 ) );
            set i = i + 1;
        end loop;
        return rtrim( newstr );


create procedure dbo.sp_iqreverse_passwd (
        in search_name  varchar(255) default '%'
        select 'set temporary option escape_character=''on'';' as 'GRANT STMT'
        union all
        select  'grant connect to ' + u.user_name +
                ifnull(sup.password,';',' identified by encrypted ''' + hextostr( sup.password ) + ''';' )
        from sys.sysuser u, sys.sysuserpassword sup
        where u.user_id = sup.user_id
                and sup.password is not null
                and lower(u.user_name) like lower(search_name);



To execute this procedure, simply log in to your SAP IQ or SAP HDLRE system and execute the procedure:

grant connect to mark identified by 'password';

call sp_iqreverse_passwd ( 'mark' );


This returns:

set temporary option escape_character='on';

grant connect to mark identified by encrypted '\x01\x2f\x7f\xdf\xf5\x5a\x65\x7d\xa4\xf5\x5f\xf3\x4b\xba\x39\x45\x7f\x9f\x80\xc2\xf5\xa3\x3d\x45\x42\xff\xe8\x0d\xbe\x72\x93\xc5\x60\xda\x97\xe9\x4d';

(2 rows)


Now, we can run this command back into the same IQ instance:

revoke connect from mark;

set temporary option escape_character='on';

grant connect to mark identified by encrypted '\x01\x2f\x7f\xdf\xf5\x5a\x65\x7d\xa4\xf5\x5f\xf3\x4b\xba\x39\x45\x7f\x9f\x80\xc2\xf5\xa3\x3d\x45\x42\xff\xe8\x0d\xbe\x72\x93\xc5\x60\xda\x97\xe9\x4d';


And, just to make sure that it works using the password of ‘password’:

[sap@tmp]$ dbisql -host localhost -port 2638 -nogui -c "uid=mark;pwd=password"

(mark)> select user_name(), db_name()
user_name() db_name()
mark        iqdemo

(1 rows)


The same code and process also works on SAP HANA Cloud, data lake relational engine.  Of course, SAP HDLRE has slightly different connectivity parameters as it is a cloud database.  One other thing of note that is quite important.  You cannot use an encrypted password on any other user.  The hash algorithm used to create the password is tied to the username.


Good luck!

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.