Hello Piers,

> 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?

There isn't, but exception handling should work. The reason for your
syntax error is the "declare variable ..." line at the beginning of the
exception handling block. All local variables in PSQL are declared at
the beginning of the code module.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/