Thursday, June 7, 2012

SQL1092N "USER" does not have the authority to perform the requested operation

Installing DB2 on machine using a domain account could at times leave that particular domain account from having rights to perform actions like CREATE, UPDATE, etc., 

The reason being, if a user logs on to a domain account and tries to access a DB2 database, DB2 goes to a Domain Controller to enumerate groups (including the Administrator's group). You can change this behavior in either of two ways:

1. Set the registry variable DB2_GRP_LOOKUP = local and add the domain accounts (or global groups) to the local Administrators group.

DB2SET DB2_GRP_LOOKUP=LOCAL
DB2STOP
DB2START

2. Update the database manager configuration file to specify a new group. If you want that group enumerated on the local machine, then you must also set the DB2_GRP_LOOKUP registry variable.

By default in a Windows NT domain environment, only domain users that belong to the Administrators group at the Primary Domain Controller (PDC) have SYSADM authority on an instance. Since DB2 always performs authorization at the machine where the account is defined, adding a domain user to the local Administrators group on the server does not grant the domain user SYSADM authority to the group. So to do that, run the following commands

DB2 UPDATE DBM CFG USING SYSADM_GROUP <global_group> 
DB2STOP
DB2START


Note : global_group named DB2ADMNS would have got created on the local machine, had you chosen the option to enable administrative security while installing DB2. Use the same group, else use any of the global groups in your network.

You have tried the above mentioned steps and you still end up seeing the same error message, the following commands might help (it does most of the times)

Run the db2set like below, to see if the DB2_GRP_LOOKUP registry value is set properly. 

C:\IBM\SQLLIB\BIN>db2set -all
[e] DB2PATH=C:\IBM\SQLLIB
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON
[i] DB2_INLIST_TO_NLJN=YES
[i] DB2_REDUCED_OPTIMIZATION=YES
[i] DB2INSTOWNER=MAGNETO
[i] DB2PORTRANGE=60000:60003
[i] DB2_GRP_LOOKUP=LOCAL
[i] DB2INSTPROF=C:\PROGRAMDATA\IBM\DB2\DB2COPY1
[i] DB2COMM=TCPIP
[g] DB2_EXTSECURITY=YES
[g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
[g] DB2SYSTEM=MAGNETO
[g] DB2PATH=C:\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00

Now append the value TOKENLOCAL to the DB2_GRP_LOOKUP registry entry like below. This works like magic most of the times.

DB2SET DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL
DB2STOP
DB2START

The above steps should have helped you see through the error. If the error still persists, reboot the machine and retry all the steps.