Need to disable multiple users

[BudDurland]

I need to disable login for all but a few users in out SQL Anywhere database while we do a software transition.  Is there a way to script that?

[VolkerBarth]

I do assume the following FAQ from this forum will be of help:

https://community.sap.com/t5/technology-q-a/how-to-temporarily-disable-connections-to-sa12-for-non-d...

[BudDurland]

Actually no, and I may have been unclear. Of the 150+ user accounts, I need to permanently disable their ability to login. Either by changing the password, or by disabling the account. A handful (10 or so) people need to continue to be able to login as normal. It seems there ought to be a way to read a system table & issue an ALTER USER statement or similar for all users except the ones that need to be able to log in

[chris_keating]

The SYSUSER table can be used for this. The challenge is how to identify the users that are to be disabled/deleted. The sample below is based on the demo database and three filters for users to process - one filter removes system users such as roles, another removes specific user types, and finally users that should be kept. You should consult a query that meets your specific needs and you should validate the users that returns in the result. 

begin -- create users for test...
    declare stmt long varchar;
    -- cleanup any test users
    select 
        list( string( 'drop user "', user_name , '"'), ';\n' ) into stmt
    from sysuser where user_name like 'u%';

    execute immediate stmt;    

    -- create test users
    select 
        list( string( 
           'create user "u', row_num, '" identified by sql' ), ';\n' )
        into stmt
    from sa_rowgenerator( 1, 100 );
    execute immediate stmt;
end;

-- this example disables users excluding system users, and
-- other identified users
begin
    declare stmt long varchar;
    -- disable users (by removing password)
    select 
        list(string(
          'alter user "',user_name,'" identified by NULL'),';\n')
    into stmt
    from sysuser 
    where   user_id < 1000000000        -- exclude roles
        and user_type not in(1,5,9,13)  -- filter user types
        and user_name not in            -- users to keep
          ( ('u20'),('u30'),('u40'),('u50'),('u60'),('u70')
           ,('DBA'),('GROUPO'),('BROWSER'),('Updater'),('ml_server'));
    execute immediate stmt;
end;

-- this example deletes users excluding system users, and
-- other identified users
begin
    declare stmt long varchar;
    -- disable users (by removing password)
    select 
        list(string(
          'drop user "',user_name,'"'),';\n')
    into stmt
    from sysuser 
    where   user_id < 1000000000       -- exclude roles
        and user_type not in(1,5,9,13) -- filter user types
        and user_name not in           -- users to keep
          ( ('u20'),('u30'),('u40'),('u50'),('u60'),('u70')
           ,('DBA'),('GROUPO'),('BROWSER'),('Updater'),('ml_server'));
    execute immediate stmt;
end;

[VolkerBarth]

Hm, I still think Mark's suggestion to use a login procedure to prevent the respective users (however you can identify those) from login would work...

As to the suggested "DROP USER" statement. Please note the relevant warning:

Dropping a user also deletes all database objects (such as tables or procedures) that they own, as well as any external logins for the user. In addition, if the user is specified in the USER clause of any services, then those services are also dropped.

This may or may not be a problem in your case. I do surely know I have dropped users (from test databases) and was surprised that objects were silently gone, I certainly had expected the engine would warn about existing objects (as a kind of FOREIGN KEY ON DELETE RESTRICED behaviour...).