Transfers the ownership of database objects from one owner to another.
alter { object_type | all } [owner.]{object_name | * }
modify owner
{ name_in_db | loginame only login_name }
[ preserve permissions ]
the type of object whose ownership is to be explicitly transferred. Specify one of the following object types:
table – user tables and proxy tables
view – views
procedure – stored procedures
function – user-defined functions
default – defaults defined separately from the creation of tables
rule – rules
type – user-defined datatypes
encryption key – encryption keys
all permitted object types. When specified as all owner.*, the ownership of all permitted objects owned by the specified owner are transferred. When specified as all owner.object_name, the ownership of permitted objects with name object_name owned by the specified owner are transferred.
indicates the current owner of the object which is determined by the owner’s database user ID (uid). Specifying owner is optional when the user is transferring the ownership of objects owned by themselves. Ownership of objects in the sysobjects table are associated with the owner’s login name and uid.
indicates the name of the object in which the ownership is to be transferred. An attempt to transferred the ownership of an object with object_name set to the same owner results in an error message.
all objects owned by owner and specified by object_type . When object_type is all, all objects owned by owner are transferred. When owner is the database owner, * is not allowed.
the database user name of the new owner to whom the ownership transfered. The user specified by name_in_db must be an existing user and cannot be a guest, role, group, or an alias.
transfers only the loginame field in sysobjects of objects involved to login_name. login_name must be a valid login in the syslogins table.
indicates whether or not to preserve explicitly granted or revoked permissions on the objects whose ownership are being transferred:
When specified – all explicitly granted or revoked permissions on the objects are preserved and the grantor of the permissions is changed to the new owner.
For example, bill granted select permission
on table bill_table to mark
with grant option. Mark then granted select permission
on table bill_table to
john. If the ownership of the table is then transferred to eric
with preserve permissions specified, mark and
john will retain their permission on bill_table.
When not specified – all existing explicitly granted and revoked permissions on the objects are removed from the system, and as a result, rows in the sysprotects table that correspond to the object are deleted.
Implicit permissions on objects are not preserved for previous owners. New owners will acquire all implicit permissions.
For example, bill is the owner of bill_table and possesses the implicit alter, delete, insert, references, select, and update permissions and explicit decrypt permission on bill_table. After an ownership transfer to eric with preserve permission specified, bill will only retain decrypt permission on bill_table.
Transfers ownership of the table named bill.author to eric:
alter table bill.author modify owner eric
Transfers ownership of the view named bill.vw_author_in_ca to eric without removing all existing explicitly granted permissions:
alter view bill.vw_author_in_ca modify owner eric preserve permissions
Transfers ownership of all tables owned by bill to eric:
alter table bill.* modify owner eric
Transfers ownership of all objects owned by bill to eric:
alter all bill.* modify owner eric
The command fails when the new owner cindy already owns an table named cindy.publisher.
alter table bill.publisher modify owner cindy
An error results when an attempt is made to transfer
the ownership of bill.publisher to
cindy because bill.publisher is not a stored
procedure.
alter procedure bill.publisher
modify owner cindy
The ownership of the following dependant objects is implicitly transferred when the ownership of the objects they depend on have been transferred:
trigger – ownership of triggers are updated along with the dependent table when the owners are the same. The ownership of a DBO owned trigger cannot be altered if the trigger was created for a non DBO owned table or view.
Declarative objects, which are defined during the table or view creation.
Defaults
Decrypt defaults
Dheck constraints
Reference constraints
Partition constraints
Computer columns
Caution should be use when transferring ownership of objects in the following system databases: sybsecurity, sybsystemdb, model, sybsystemprocs, sybsyntax, dbccdb, and tempdb.
Do not transfer ownership of system objects supplied and managed by Sybase, such as but not limited to, user tables with spt_ prefix, system stored procedures with the sp_ prefix, and monitor tables. Doing so can render the system database unusable.
Transfer of encryption keys to an owner who owns a copy of the key is not allowed and the command will fail.
Changing the owner of encryption keys does not effect the assignees of the encryption key copies.
ANSI SQL – Compliance level: Transact-SQL extension.
System security officer (SSO) users are allowed to use this command to transfer the ownership of objects.
Only SSO users and encryption key owners can transfer the encryption key ownership.
Database owner (DBO) users and users who are explicitly or implicitly aliased to the DBO are allowed to transfer the ownership of objects with a type other than encryption keys with the following restrictions:
Database owners are not allowed to transfer the ownership of objects they concretely own. An object is identified as concretely owned by the database owner if it includes DBO_UID for the value of sysobjects.uid, and NULL or the database owner’s login name for the value of sysobjects.loginame.
The functionality of transferring the ownership of multiple objects in one command is disabled for safety reasons and the transfer of DBO objects must be in the form of dbo.object_name.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
124 |
alter |
alter .. modify owner |
|
