Unlock SAP* and DDIC in DB2 9.7 V SAP ECC6.0

Hi All,
If SAP* and DDIC logins are locked and you are not able to login through SAP GUI then we can unlock SAP* and DDIC at database level by resetting the flags of SAP* and DDIC to 0 in the table USR02 where all the user records are saved. When the logins are locked out then the flag value will be 128. If we change it to 0 at database level then it will get unlocked.

SAP* Unlocking

Instead of deleting user -

SAP* try to unlock the user by logging into the system with some other user. Else unlock from DB level as per the steps given below.

Kindly ensure that SAP* user is not deleted from R3 or DB level.

In case if you find that the SAP* is deleted and you are able to login with pass as password then please create

SAP* user with copy of DDIC or TCSDEV users in SU01.

Even you can create a new user SAP* with roles:

SAP_ALL and SAP_NEW.

The password for

TCSDEV user is present in private ark.

Important:

In 7.10 systems if you delete the SAP* user with the report (TK_DELETE_SAP) or from DB, then in SU01 the create and copy buttons will not be present. This happens since CUA is maintained on that system. In such cases only system owner can create the user in the system. So you can send a mail to the system owner asking him to create SAP* user.

The SQL commands in different databases are given below:

[1]{A} ORACLE <= 8.x

• Logon to database server and switch to ora<SID> user (e.g. oraalr) under UNIX or <sidadm> under Windows. Start the database server manager with the command: svrmgrl

• connect internal Now you're able to execute SQL statements:

• to view the entries of the "sap*" user type in following command: select MANDT, BNAME, UFLAG from <db_schema>.USR02 where MANDT='000' and BNAME='SAP*'; (maybe you have to change "mandt" to your client)

• To simply unlock SAP* in client 000, without having to delete it: update <db_schema>.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT='000';

• exit 􀃆 exit the server manager

[

1]{B}ORACLE >= 9.x

• Logon to database server and switch to ora<SID> user (e.g. oraalr) under UNIX (<sidadm> under Windows). Under Windows check correct settings of environment (ORASID, ...). Start the database server manager with the command: sqlplus "/as sysdba" Now you're able to execute SQL statements:

• to view the entries of the "sap*" user type in following command: select MANDT, BNAME, UFLAG from <db_schema>.USR02 where MANDT='000' and BNAME='SAP*'; (maybe you have to change "mandt" to your client)

• To simply unlock SAP* in client 000, without having to delete it: update <db_schema>.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT='000';

• exit 􀃆 exit the server manager

Find out existing schemas: select * from all_users;

[2] DB6 (DB2 UDB for Unix/Linux/Windows)

• Logon to database server 1. Switch to db2<sid> user (e.g. db2qnn) under UNIX or 2. logon as <sidadm> under Unix/Windows

• Start the SQL mode with command: db2 (Unix) db2cmd (Windows) Now you're able to execute SQL statements (on Windows: db2 in front of the SQL statement):

• to view the entries of the "sap*" user type in following command: select MANDT, BNAME, UFLAG from <db_schema>.USR02 where MANDT='000' and BNAME='SAP*' (maybe you have to change "mandt" to your client)

• To simply unlock SAP* in client 000, without having to delete it: update <db_schema>.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT='000'

• quit Find out existing schemas: select * from syscat.dbauth If UFLAG value is other than 0 when you execute select command then the user is locked.

[3] SAPDB/MAXDB

• Logon to database server 1. Switch to sqd<SID> user (e.g. sqdyak) under UNIX or 2. logon as <sidadm> under Unix/Windows Start the SQL mode with the command: dbmcli -d <SID> -u control,<password> -uSQL <db_schema>,<password> sql_execute <sql statement> alternate sqlcli -d <SID> -u <db_schema>,<password> --> (version >= 7.5) <sql statement> Now you're able to execute <sql statement>:

• To view the entries of the "sap*" user type in following command: sql_execute select MANDT, BNAME, UFLAG from <db_schema>.USR02 where MANDT='000' and BNAME='SAP*' (maybe you have to change "mandt" to your client)

• To simply unlock SAP* in client 000, without having to delete it: sql_execute update <db_schema>.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT='000'

• quit or q(sqlcli)

To find out which schemas exist:

•

dbmcli -d <SID> -u control,<password>

• sql_execute select * from users

[4] MSSQL

• Logon to database server as <sidadm> in D_WDF_R3 domain

• invoke: Start --> Programs --> Microsoft SQL Server --> Query Analyzer

• enter: SQL server : '.' means current server connect using : Windows authentication

• choose needed DB instance in selection field Now you're able to execute SQL statements:

• To view the entries of the "sap*" user type in the following command: select MANDT, BNAME, UFLAG from <db_schema>.USR02 where MANDT=000 and BNAME='SAP*' (maybe you have to change "mandt" to your client; <db-user> is case sensitive)

• To simply unlock SAP* in client 000, without having to delete it: update <db_schema>.USR02 set UFLAG=0 where BNAME='SAP*' and MANDT=000

• execute --> press F5

• close Query Analyzer

Kindly be very careful when you are using UPDATE command because there is no way to revert back. The only way is to restore the whole DB.

Because USR02 is a buffered table please logon to any client and execute /$tab in the transaction code field to synchronize the buffers.

To know the <db_schema> on different databases you can follow the below screen shots:

1. Oracle:

2. DB2:

3. MAXDB/SAPDB:

4. MSSQL:


Regards,

Alihamed.sa
Basis Administrator.