Users missing from DBC.USERSV

General
Enthusiast

Users missing from DBC.USERSV

Hello .. When I run the below statement with my ID, I get about 40+ rows. When I run the same SQL with the DBC id, only 4 rows are returned, which means, the user records that I thought were missing in dbc.users are there, I just cannot see them. In researching, I saw comments that the user (me) may not have create profile / drop profile access. So I logged on with the DBC and granted create/drop profile to my id, logged out and came back in. When I run the script below, I still see the same number of records missing from dbc.users. What am I missing now ?? Thanks in advance.

 

SELECT DATABASENAME FROM DBC.DATABASESV 
 WHERE DBKIND = 'U'
 MINUS
SELECT USERNAME FROM DBC.USERSV
 ORDER BY 1 ;

 

3 REPLIES 3
Ambassador

Re: Users missing from DBC.USERSV

dbc.UsersV is an X-view without X, i.e. the submitting user must have any right or must be an owner, check the sorce code.

 

That's why dbc sees all users (besides those dummy users all, public, default, externaluser).

An end user usually gets a single row, his own user.

Enthusiast

Re: Users missing from DBC.USERSV

Thanks for the quick response, but that doesn't really answer my question, sorry. Out of about 2000+ users, I get the issue for only 40+. I can see all other users thru both databasesv and usersv. I can create users, and can see users created by others also. I do have the create / drop profile privilege, which is on a global level, not at the user level. Still, why is that I cannot see these 40+ users ? These users may be created quite a long time ago, but still if I have the create / drop privilege, I should be able to see these users, right ??

Highlighted
Ambassador

Re: Users missing from DBC.USERSV

There's no global DROP priviledge, you must be an owner of the user or have DROP rights on the user.