Hello,
I am in need of changing the owner of a database and all the metadata
objects it contains from SYSDBA to one of my choice.

I have seen that a restore is enough to take care of the owner for the
database and the system tables, while the user tables retain their
original owner. Extracting the metadata to a script + editing + data
pump is not feasible with my environmental constraints, so I googled a
bit and finally came up with this procedure:

create procedure R_CHANGE_OWNER (
USER_NAME varchar(31))
as
begin
update
RDB$RELATIONS R
set
R.RDB$OWNER_NAME = :USER_NAME
where
R.RDB$SYSTEM_FLAG = 0
or R.RDB$SYSTEM_FLAG is null;
update
RDB$PROCEDURES P
set
P.RDB$OWNER_NAME = :USER_NAME
where
P.RDB$SYSTEM_FLAG = 0
or P.RDB$SYSTEM_FLAG is null;
end

Note: luckily enough I don't have to worry about roles.

Is it so wrong an approach? I suppose so, but then I haven't found a
suitable alternative. I am asking because executing and committing
this procedure on my database crashes Firebird SuperServer 1.5 on
WinXP. The crash is at commit time. Even the plain

update
RDB$RELATIONS R
set
R.RDB$OWNER_NAME = :USER_NAME
where
R.RDB$SYSTEM_FLAG = 0
or R.RDB$SYSTEM_FLAG is null;

triggers the crash.

Can anyone help?

Ciao
--
Nando mailto:nandod@...