Link to home
Start Free TrialLog in
Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on

DB2 Restore on new install not working

DB2 10.5 on Windows

So I am trying to teach myself how to install DB2 and restore a dev database.  So I install DB2 10.5 on windows without issue.  I then run the sample database setup and I can connect to it no problem.

When I copy over the DEV database backup and try to restore it, problem.  I get this.

C:\Windows\system32>db2 restore database DEV FROM X:\dbbackups
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: "xxx".  SQLSTATE=00000

Now XXX is in the Admin group on the windows server and it is running the DB2 instance service.  It should have full control of the server.  I looked at the sys roles (sysmon, sysadm,sysctrl, and sysmaint group names and set them all to Administrators which is the admin group name on the windows server.  I just don't get it.  What am I missing?

FRUSTRATED IN STL

Jim
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi Jim,

Let's make sure that DB2 recognizes the administrative permissions of that user.

  SELECT * FROM TABLE (sysproc.auth_list_groups_for_authid('xxx'))

And does the DEV database already exist on this server?  You may need the "REPLACE EXISTING" or "REDIRECT" clause on the RESTORE statement.

Kent
Avatar of Jim Youmans

ASKER

The database I am trying to restore does not exist on the new server.  Only the SAMPLE database does.

So the account I use to run the server is AITDB2Service and when I run the above query I get...

AITDB2ADMINS
DOMAIN USERS

When I do the same for my network account which is an admin on the server I get...

BITBUCKET-SOFTWARE-USERS,CENTRIC_LEGALFEESDB_RWM,CENTRICIT,CITRIX_ACCESSMANAGEMENTCONSOLE,CONFLUENCE-USERS,
DEPSVCSRO,DEV_BIDATAREAD_ADMIN,DOMAIN USERS,EXTRAHOP_READONLY,ICINGANORMALACCESS,IS,IT,JIRA-SOFTWARE-USERS,
LASTPASSACCESS,LINUXLOGONKCN,LINUXLOGONKEEFEDATACENTER,OFFICE_365_USERS,PRD_BIDATAREAD_ADMIN,
PRD_PHONESYS_MGR,QAT_BIDATAREAD_ADMIN,QAT_PHONESYS_MGR,SCCM_REPORT_PURCHASING,SCOMSQLADMINS,
SCSM_NOTIFY_APPLICATION DEVELOPMENT,SCSM_NOTIFY_BUSINESS INTELLIGENCE,SCSM_NOTIFY_DATABASE SERVICES,
SCSM_SG_APPLICATION DEVELOPMENT,SCSM_SG_BUSINESS INTELLIGENCE,SCSM_SG_DATABASE SERVICES,
SCSM_TIER1,SCSM_TIER2_STANDARD,SW_DPA_ADMIN,SW_SQLDBAS,VMWARE_READ_ONLY,VPN_ACCESS,WHSLOCATORDB_RWM,
WSUS ADMINISTRATORS

So what should I see?  For DB2 permissions?
AITDB2Service seems to be in the AITDB2ADMINS group, so use it.
I tried but it gives me the same error.  How do I add a user to the AITDB2ADMINS group?
Through the Windows interface.

Right click in My Computer, then Manage, then Users.
I have obviously done something wrong.  Looking at my groups in windows there is a DB2ADMNS group and a DB2USERS group. Those were created when I installed DB2.  There is no AITDB2ADMINS group.  Not sure where that is coming from.

I have 3 users in the DB2ADMNS group, jyoumans (me), CVADMIN (commvault), and AITDB2Service (which runs DB2 server).  I have tried the restore with jyoumans and AITDB2Service, both get the same not authorized error.

I installed DB2 with the AITDB2Service user.  I checked the DB2 windows service and AITDB2Service is listed as the LOG ON AS of that service.

What am I missing?

Jim
Are you logging in with a different user to try the restore or are you passing the username/password to the RESTORE command?  I've never seen this behavior and would be shocked if it happened when the username is passed on the command.
I logged into the server using the AITDB2Service ID and then ran the restore like this.

db2 restore database DEPSVCS user AITDB2Service password "xxx' from x:\backups

And 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".  SQLSTATE=00000

This should not be this hard.  Please note that DEPSVCS is not on the new server.  Just sample.

Jim
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So, I ended up wiping out all the sys*_group definitions and leaving them to NULL so that the server admin group would default to them.  Then I stopped and restarted the db2 instance and then bounced the server.  That seems to have worked.  Thank you so much!!