Here is how to find lost SAP* and DDIC passwords:
1.) Logon as DB admin (on oracle: ora, for informix: informix)
2.) Run the SQL program of your database (on oracle: sqlplus, for informix: dbaccess)
3.) IMPORTANT: Make a current snapshot of the table. Do an export on table USR02 (Oracle: use the command exp, Informix: use dbexport) ... Alternative: Create a table copy of USR02 This will be used if anything goes wrong.
4.) Now do a SELECT command on table USR02. The idea is to check if SAP* is present in the client you want. (Command: SELECT * FROM USR02 WHERE MANDT=\'XXX\' and BNAME=\'SAP*\' ... MANDT here is the client) ... this is an optional step ...
5.) The next step is to make a copy of the SAP* table record (to be used on step #9). For Oracle, create a temporary table with USR02 structure and insert the value SAP* from the client that you want. For Informix, you may just copy the exported USR02 data and edit it using vi to delete other records other than SAP* .. or use the same steps as in Oracle.
6.) Delete the record SAP* ON THE REQUIRED CLIENT ONLY on table USR02. (Command: delete from USR02 where MANDT=\'XXX\' and BNAME=\'SAP*\').
Commit the changes, if needed.
7.) Now logon to SAP. Use the ID SAP* and password PASS (or 06071992). You can now edit the password for the DDIC user ID. You have to create a user ID or give authorization to DDIC (if needed) to access transaction SU01.
Code
For Example , in my SAP IDES System , I can do like below Step:
User DDIC can be unlocked using any user who has administration privelages(example: SAP*) using SU01.
Infact as benjamin said you cannot recover the encrypted password from SQL database, the only alternative is unlock and reset the password.
there is one situation where in you have to use a SQL query, that is when u create a new client and SAP* User get locked , since you have no other user to unlock it(assuming you are logging in for the first time and got SAP* locked)
SQL Query is as follows :
delete from er1.USR02 where BNAME=\'DDIC\' and MANDT=\'000\'
This is possible because the SAP* user is hardcored to kernel , when you delete this user from database, SAP automatically gets this user from the kernel.