alter...modify owner

Syntax

alter { object_type | all } [owner.]{object_name | * }
	modify owner
	{ name_in_db | loginame only login_name } 
	[ preserve permissions ]

Parameters

object_type – 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 – 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.
owner. – 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.
object_name – 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.
name_in_db – 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.
loginame only login_name – transfers only the loginame field in sysobjects of objects involved to login_name. login_name must be a valid login in the syslogins table.
preserve permissions – 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 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 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 only retains decrypt permission on bill_table.

Examples

Example 1 – Transfers ownership of the table named bill.author to Eric:
```
alter table bill.author
    modify owner eric
```
Example 2 – 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 
```
Example 3 – Transfers ownership of all tables owned by Bill to Eric:
```
alter table bill.* 
    modify owner eric 
```
Example 4 – Transfers ownership of all objects owned by Bill to Eric:
```
alter all bill.*
    modify owner eric 
```
Example 5 – The command fails when the new owner Cindy already owns an table named cindy.publisher.
```
alter table bill.publisher
    modify owner cindy
```
Example 6 – An error results when an attempt is made to transfer the ownership of bill.publisher to Cindy becausebill.publisher is not a stored procedure.
```
alter procedure bill.publisher
```
```
    modify owner cindy
```

Usage

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

When transferring ownership of objects:

Use caution in the following system databases: sybsecurity, sybsystemdb, model, sybsystemprocs, sybsyntax, dbccdb, and tempdb.
Do not transfer ownership of system objects supplied and managed by SAP, 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.

When transferring encryption keys:

To an owner who owns a copy of the key is not allowed and the command fails.
Changing the owner of encryption keys does not effect the assignees of the encryption key copies.

You cannot use the alter ... modify owner command to change the owner of a precomputed result set. To change the owner, drop the precomputed result set and re-create with the new owner.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for alter... modify owner differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must have the `alter any object owner` privilege for objects other than encryption key. Only encryption key owners or user with the following privilege (based on encryption key type) can transfer the encryption key ownership: column encryption key – `manage column encryption key` master key – `manage master key` service key – `manage service key` The database users (with `alter any object owner` privilege) who are explicitly or implicitly aliased to the database owner 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 database owner user ID for the value of `sysobjects.uid`, and NULL or the database owner’s login name for the value of `sysobjects.loginame`.
Disabled	With granular permissions disabled: 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`.

Setting

Description

Enabled

With granular permissions enabled, you must have the alter any object owner privilege for objects other than encryption key.

Only encryption key owners or user with the following privilege (based on encryption key type) can transfer the encryption key ownership:

column encryption key – manage column encryption key
master key – manage master key
service key – manage service key

The database users (with alter any object owner privilege) who are explicitly or implicitly aliased to the database owner 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 database owner user ID for the value of sysobjects.uid, and NULL or the database owner’s login name for the value of sysobjects.loginame.

Disabled

With granular permissions disabled:

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.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	124
Audit option	alter
Command or access audited	alter .. modify owner
Information in `extrainfo`	Roles – current active roles Keywords or options – one of: `USER TYPE` `owner.obj_name` – if you are changing user-defined types’ ownership `NEW OWNER` – `name_in_db` `PRESERVE PERMISSIONS` – if the option is specified `NEW LOGINAME` – `login_name`, if `LOGINAME ONLY` `login_name` is specified Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect