Nando,

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

There is more involved as shown above.

You could give our FBOwnerMigrator utility a try. Let me
know if you are interested.


Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com