There are several times that you may have to restore from a production box to a beta or a QA environment as a different user. In that scenario, the restore commands will work just fine but you will receive authorization errors even when the id you used to restore has SYSADM.

Cause:

Starting DB2 V9.7, SYSADM no longer has implicit DBADM privileges due to a change in security policies, You may see SQL errors like SQL0551N, SQL0552N or SQL3020N

Resolution:

Set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable BEFORE performing the restore. An instance bounce is required.

db2stop
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2set -all | grep -i db2_restore_grant
[i] DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2start

Once you set this variable, SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that issues the restore operation.

Share This