DB2 SYSADM_GROUP not working

Jim Youmans
Jim Youmans used Ask the Experts™
on
DB2 10.5 on Windows Server 2012 R2

I am trying to get DB2 to work with the DB2ADMN group on a windows server.  I want any account in the DB2ADMN group to have sysadmin rights on DB2.  From everything I have read and from other questions I have posted here, this should work, but it does not.  
Here is what I have done.

Create the windows group DB2ADMNS and add my domain1\AITDB2Service account to it.  This is a domain account and it runs the DB2 service under Windows and it is a local admin on the windows server.

db2set DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL
db2 update dbm cfg using sysadm_group DB2ADMNS
DB2STOP
DB2START


When I look at the DBM CFG see this.

SYSADM group name                        (SYSADM_GROUP) = DB2ADMNS
 SYSCTRL group name                        (SYSCTRL_GROUP) =
 SYSMAINT group name                     (SYSMAINT_GROUP) = xxx
 SYSMON group name                        (SYSMON_GROUP) = xxx


I can log into DB2 using AITDB2Service no issue, even though when I look at the SECURITY tab in TOAD, the AITDB2Service user is not there.  I can do selects and updates and such on tables, but I can't do CREATE TABLE or run a backup. When I try to run a backup I get...

SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "AITDB2SERVICE".


If I try to grant privileges using AITDB2Service I get this.

GRANT DBADM WITH DATAACCESS WITH ACCESSCTRL ON DATABASE TO USER ANYUSER;
 ERROR [42502] [IBM][DB2/NT64] SQL0552N  "AITDB2SERVICE" does not have the privilege to perform operation "GRANT".


So what am I doing wrong?  There does not seem to be an account on this database that has SYSADMIN rights, so I can't use it to grant rights to AITDB2Service.  This is a test database that I copied from production, did I do something wrong when I restored it?

Any help would be greatly appreciated.

Jim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator / Software Engineer
Commented:
Hi,

What user did you use to create the database ? That user is the owner and primary sysadmin.
You will need to use that user and look at the  SYSCAT.DBAUTH catalog view and see what users have what privilege.

And only the primary sysadmin (database owner ) can do the initial grants to users/groups inside the database.

Also I think that the TOKENLOCAL is probably conflicting with the local group config

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/t0005914.html

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0055077.html


Change the DB2_GRP_LOOKUP=LOCAL to only include LOCAL and
after issuing the command, you must stop and start the Db2 database instance for the change to take effect. Add local groups and include domain accounts or global groups in the local group which is DB2ADMNS.

Regards,
     Tomas Helgi
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

Did you manage to solve this or do you need any more help on this ?

Regards,
    Tomas Helgi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial