sp_unbindrule

Description

Unbinds a rule from a column or from a user-defined datatype.

Syntax

sp_unbindrule objname [, futureonly [, “accessrule” | “all”]]

Parameters

objname

is the name of the table and column or of the user-defined datatype from which the rule is to be unbound. If the parameter is not of the form “table.column”, then objname is assumed to be a user-defined datatype. Unbinding a rule from a user-defined datatype also unbinds it from columns of the same type. Columns that are already bound to a different rule are unaffected.

futureonly

prevents columns of the specified user-defined datatype from losing their rules. It is ignored when unbinding a rule from a column.

accessrule

indicates that you are unbinding the access rule bound to objname.

all

specifies that you are unbinding all rules bound to objname.

Examples

Example 1

Unbinds the rule from the startdate column of the employees table:

sp_unbindrule "employees.startdate"

Example 2

Unbinds the rule from the user-defined datatype named def_ssn and all columns of that type:

sp_unbindrule def_ssn

Example 3

The user-defined datatype ssn no longer has a rule, but existing ssn columns are unaffected:

sp_unbindrule ssn, futureonly

Example 4

You can use the all parameter to unbind both accesss rules and domain rules. For example, to unbind all the access rules and domain rules on the publishers table:

sp_unbindrule publishers, null, "all"

To unbind the access rule from a user-defined datatype for subsequent uses of this datatype, issue:

sp_unbindrule def_ssn, futureonly, "accessrule"

To unbind both access rules and domain rules for subsequent uses of this datatype, issue:

sp_unbindrule def_ssn, futureonly, "all"

Example 5

This access rule is bound to the publishers table:

sp_bindrule empl_access, "publishers.pub_id"

To unbind this rule, issue the following:

sp_unbindrule "empl_access", NULL, "accessrule"

Usage

Permissions

You must be the table owner or datatype owner to execute sp_unbindrule. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

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

See also

Commands create rule, drop rule

System procedures sp_bindrule, sp_helptext