Hello

We are trying to migrate some existing users to the new RDB$ADMIN using some dynamic SQL. We have the following SQL

for
--Gets a list of users from our system
select ib_username from user_group_users where user_group_no = 1
into :v_username
do
begin
--Give the user admin rights in our database
v_grant_statement = 'GRANT RDB$ADMIN TO ' || :v_username || ' GRANTED BY SYSDBA ';
execute statement v_grant_statement;

--Give the user admin rights in the security database
--The security database MUST be Dialect 3. This should be handled by the installer
v_grant_statement = 'ALTER USER ' || :v_username || ' GRANT ADMIN ROLE ';
execute statement v_grant_statement;
end

However if the value in :v_username does not exist in the Security2.fdb then we get an exception. We tried a "when any do" to add the user if there was an error;

when any do
begin
--user is not in the security2.fdb, so add them
declare variable v_grant_statement2 varchar(51);
v_grant_statement2 = 'CREATE USER ' || :v_username || ' password ''' || :v_username || ''' ';
execute statement v_grant_statement2;
end

however this brings up syntax errors when run. Is there an easy way in SQL to test if a user exists in the Security2.fdb?

Thanks,

Piers Smith
Development Team Lead
Communicare Systems Pty Ltd
Tel: 08 6212 6900 Fax: 08 6212 6980
piers.smith@...<mailto:piers.smith@...>
www.communicaresystems.com.au<http://www.communicaresystems.com.au/>

[Verbose footer content removed by Moderator]
[Non-text portions of this message have been removed]