Question & Answer
Question
How do I change owner of constraint?
Answer
A user cannot be dropped if he owns constraints. In some cases, changing the owner of a table to another database user will not change the owner of constraints. This will not allow dropping of a database user.
1. The Support Contributed Toolkit contains a script to check if a particular user owns any constraints. Run the script as follows:
/nz/support/contrib/<NPS Release>/bin/nz_find_object_owners <database_user>
The output appears similar to the following:
bash-3.00$ ./nz_find_object_owners kk
KK owns constraint CHOICEPPPK in database STGNG
KK owns constraint CHOICESORTPK in database STGNG
KK owns constraint CHOICEUNIONPK in database STGNG
2. To change the ownership of constraints, run the following SQL making sure to provide the correct database user account name:
update _t_object set objowner = (select usesysid from _t_user where usename = '<database_user>') whereobjowner = (select usesysid from _t_user where usename = 'admin');
If the Support ContributedToolkit is on the NPS server, there is a script that can help generate these SQL statements for you. The script is nz_change_owner. It is used as follows:
/nz/support/contrib/4.6/bin/nz_change_owner -h
Usage: nz_change_owner -from -to [-db database] Purpose: To globally change the owner of a set of objects.
Inputs:
-from All objects owned by this user will be affected. This is a required field. You must specify a valid username. This script will not allow you to globally change the ownership of all objects owned by 'ADMIN'.
-to The user whom you want to transfer ownership to. This is a required field. You must specify a valid username.
[-db ] An optional database name. If specified, then only objects within this database (to include the database itself) will be looked at. Otherwise all objects in all databases, as well as any global objects (users or groups), will be looked at to see if they were created by the specified "-from" user.
Outputs:
A set of SQL/DDL statements of the form ALTER ... OWNER TO ... ;
Note: This script doesn't actually make the changes. It simply outputs a stream of SQL statements that can be used to do the work. All you need do is simply pipe that back into nzsql. For example, nz_change_owner ... | nzsql
Historical Number
NZ892358
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21571233