create rule

Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype, and creates access rules.

Syntax

create [or replace] [{and | or} access]] rule
	[owner.]rule_name 
	as condition_expression

Parameters

Examples

Usage

Objects that are dependent on replaced rules:
  • Columns from many tables can be bound to the replaced rules.
  • User defined datatypes can be bound to the replaced rules.
Procedures that access these columns will be recompiled when the rule is replaced and the procedure is executed.

See also sp_bindrule, sp_help, sp_helptext, sp_hidetext, sp_rename, sp_unbindrule in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Entry-level compliant.

To create rules using ANSI SQL-compliant syntax, use the check clause of the create table statement.

Permissions

Any user who impersonates the rule owner through an alias or setuser cannot replace the rule.

The permission checks for create rule differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must have the create rule privilege to create a rule. To create a rule for another user, you must have the create any rule privilege.

You must be the rule owner to replace the rule.

Disabled
With granular permissions disabled, you must be the database owner, a user with sa_role, or have the create rule privilege to create a rule. To create a rule for another user, you must have sa_role.

You must be the rule owner to replace the rule

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

13

Audit option

create

Command or access audited

create rule

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

  • or replace – for create or replace

Related reference
update
insert
create table
alter table
create default
drop rule
drop table