ANSI_PERMISSIONS option [TSQL]

Function

Controls permissions checking for DELETE and UPDATE statements.

Allowed values

ON, OFF

Default

ON

Description

With ANSI_PERMISSIONS ON, SQL92 permissions requirements for DELETE and UPDATE statements are checked. The default value is OFF in Adaptive Server Enterprise. Table 2-6 outlines the differences.

Table 2-6: Effect of ANSI_PERMISSIONS option

SQL statement

Permissions required with ANSI_PERMISSIONS OFF

Permissions required with ANSI_PERMISSIONS ON

UPDATE

UPDATE permission on the columns where values are being set

UPDATE permission on the columns where values are being set

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 table

DELETE permission on table.

SELECT permission on all columns appearing in the WHERE clause.

The ANSI_PERMISSIONS option can be set only for the PUBLIC group. No private settings are allowed.