Special requirements for SQL92 standard compliance

When you have used the set command to turn ansi_permissions on, additional permissions are required for update and delete statements. Table 12-3 summarizes the required permissions.

Table 12-3: ANSI permissions for update and delete

Permissions required: set ansi_permissions off

Permissions required: set ansi_permissions on

update

update permission on columns where values are being set

update permission on columns where values are being set

and

select permission on all columns appearing in the where clause

select permission on all columns on the right side of the set clause

delete

delete permission on the table

delete permission on the table from which rows are being deleted

and

select permission on all columns appearing in the where clause

If ansi_permissions is on and you attempt to update or delete without having all the additional select permissions, the transaction is rolled back and you receive an error message. If this occurs, the object owner must grant you select permission on all relevant columns.