Link to home
Start Free TrialLog in
Avatar of choracy
choracyFlag for Cyprus

asked on

DB2 cannot create database err=SQL1092N as a domain user

I have very little DB2 experience.

I've just installed DB2 9.7 FP4 on Win2008R2x64 using a Domain account having local Admin privileges. (setup.exe "Run as Administrator").

Selected Server authentication. Local Groups and accounts for DB2 were created.

Added domain account to local DB2ADMNS and DB2USERS.

I tried almost all possible values and combinations for DB2_GRP_LOOKUP (local, token, domain) but could not create a database.

Got the error : SQL1092N  "SVC_USER  " does not have the authority to perform the requested command or operation.

I suppose logging on windows as DB2Admin will create the database ok, also if I start the DB2 instance as the domain SVC_USER, will allow database creation but I do not want to try either.

Need a way to be able to create databases using the SVC_USER domain account.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi choracy,

Lot into the command line processor and run this command:


The user needs the CREATEDB or DBADM authority.

You can add them from the admin account if they aren't there.

Good Luck,
Avatar of mustaccio
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of choracy


I did not setup manually any value to SYSADM_GROUP.

My domain account IS a member of local Administrators AND DB2ADMNS.
Hi Choracy,

Let's look at what the user's permissions really are.  Run the GET AUTHORIZATIONS command.

db2 => connect to mydb

   Database Connection Information

 Database server        = DB2/NT 9.7.4
 SQL authorization ID   = USER1
 Local database alias   = MYDB

db2 => get authorizations

 Administrative Authorizations for Current User

 Direct SYSADM authority                    = NO
 Direct SYSCTRL authority                   = NO
 Direct SYSMAINT authority                  = NO
 Direct DBADM authority                     = YES
 Direct CREATETAB authority                 = NO
 Direct BINDADD authority                   = NO
 Direct CONNECT authority                   = NO
 Direct CREATE_NOT_FENC authority           = NO
 Direct IMPLICIT_SCHEMA authority           = NO
 Direct LOAD authority                      = NO
 Direct QUIESCE_CONNECT authority           = NO
 Direct CREATE_EXTERNAL_ROUTINE authority   = NO
 Direct SYSMON authority                    = NO

 Indirect SYSADM authority                  = YES
 Indirect SYSCTRL authority                 = NO
 Indirect SYSMAINT authority                = NO
 Indirect DBADM authority                   = NO
 Indirect CREATETAB authority               = YES
 Indirect BINDADD authority                 = YES
 Indirect CONNECT authority                 = YES
 Indirect CREATE_NOT_FENC authority         = NO
 Indirect IMPLICIT_SCHEMA authority         = YES
 Indirect LOAD authority                    = NO
 Indirect QUIESCE_CONNECT authority         = NO
 Indirect CREATE_EXTERNAL_ROUTINE authority = NO
 Indirect SYSMON authority                  = NO

Open in new window

This will tell you what you can do.  Then it's a simple matter of determining what authority you want the user to have.

Avatar of choracy


Ken I tried to run 'GET AUTHORIZATIONS' but the command was not valid.

The documentation said it was deprecated since 9.5 and proposed the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function, which did not tell me much.
> which did not tell me much

Is this some kind of a game, where we're supposed to guess what you believe it didn't tell you? OK, I'm in. Please post the 1st line of the output of the query. Tomorrow I'll ask you to post the 2nd line.
Avatar of choracy


Wow! we have a wise guy here !

Guess what ? Both GET AUTHORIZATIONS and AUTH_LIST_AUTHORITIES_FOR_AUTHID, need the user to be connected to a database !

Guess again! My problem is that I CANNOT create a database!  But you are too clever to figure that out aren't you!

I ran the command on a similar environment (OS,DB), only the installation was performed by the local Administrator and database creation has no problem. Anyway checking AUTH_LIST, shows both DB2Admin and local Administrator have a SYSADM=Yes on Group. Their common group is local Administrators.
Then added a domain user in local Administrators group and checked for the domain user. SYSADM=No, IMPLICT_SCHEMA=YES.

I understand that there is a default or Inherited security from Groups or Roles the user belongs to and a Direct one which is assigned directly to the user.

I believe in that DB2 somehow fails to see that the domain user is a member of local Administrators.

Doing some research found that in some cases, people having the same problem managed to resolve it, by setting the DB2_GRP_LOOKUP variable. I tried a few but could not resolve.

I guess I can add the domain user to SYSADM_GROUP and most probably will work but I do not like using direct security assignments if I can avoid them.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of choracy


Thanks Kent, I will have access to the system on Thursday morning and I will give it a try.

Actually there are several things I want to try with local and domain users.

I think DB2Admin is the instance owner, yes?

Will keep you posted.
DB2ADMIN is MY instance owner.  Yours could well be different.  :)

And their are other tables/views to query for other user-level permissions within a database, but they aren't applicable to the problem at hand.

Avatar of choracy


Ok, findings.

First, I verified that a database connection is absolutely needed to use GET AUTHORIZATIONS.

Second, a user needs SYSADM (and perhaps SYSCTRL in 9.7)  authority to create database.

Users who get SYSADM authority are described here:

Anyway I gave DB2ADMNS both SYSADM and SYSCTRL authority but still my domain user, member of DB2ADMNS could not create sa database.

I also run into a confising statement in DB2 9.1 documentation saying
 "For a domain user to have SYSADM authority, they must belong to the local Administrators group or the Administrators group at the domain controller. Since the DB2 database server 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 this group." ???

Confusing and too complex for me. This should be quite straightforward.

So, I logged on as DB2ADMIN, created the databases and granted 'dbadmin' to the domain user.
Hi Choracy,

Sorry that you find it confusing.  Reference material often tends to read that way so that they can cover all of the bases.

DB2 authenticates users by querying the underlying operating system.  If all of the users are defined on the system (as would often occur on a unix system), the authentication uses the definitions on that system even if the user is also defined on the network (such as an Active Directory).

That confusing paragraph is trying to say that if a user is given SYSADM authority (or any authority, for that matter) it MUST be given at the level where the host O/S will validate the user.