DB2 – How to Add a User with a Password

Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.

So I did the following:

1. Opened the DB2 GUI (was easiest to do this way).
2. Connected to the desired database as db2admin.
3. Added my logon to the database as a user.
4. GRANT all authorities to that id (my id).
5. Disconnect.
6. Connect to the desired database as my id.
7. GRANT all authorities to db2admin.
8. Disconnect.

Voila! The db2admin logon now as all authorities.

EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the instance owner (in my case the db2admin id) have all the authority needed to interact with the database. I could have actually just commented out those grant lines above in the script. Those were left over from a script which ran against an older version of DB2. I have also found if I need to have the instance owner DBADM after doing a restore to database A from database B, it is easiest just to set the registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available in Fix Pack 2 and above). Then I don't have to try to grant instance owner DBADM. It automatically is granted that to any database restored into the instance. If you are not at Fix Pack 5, you have to bounce the instance for this to take affect.

The short answer to this problem is that you need to set the DB2 registry variable DB2_GRP_LOOKUP to local.

As a local administrator on your PC (i.e. not using your domain account), start a DB2 Command Window and issue the following:

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

The reason this occurs has to do with how DB2 enumerates which groups a user belongs to upon successful authentication. In a Windows Domain environment, the default group enumeration behavior is to ask the system that authenticated the user: If a user is authenticated by the local machine, DB2 asks the local machine what groups belongs to; if the user is authenticated by the domain, DB2 will ask the domain what groups the user belongs to - and it will not ask the local machine.

So, when you connect to DB2 using your domain ID, DB2 asks the domain controller what groups you belong to, so it does not see that your ID belongs to the Administrators group on your local PC.

When you set DB2_GRP_LOOKUP=local, this modifies this behavior: It tells DB2 to always enumerate groups from the local machine, regardless of whether the user was authenticated by the domain or the local machine.

After doing this, DB2 will see that your Domain ID belongs to the local Administrators group, and you'll end up with full SYSADM privileges (and the ability to create a database).