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

Executing sp_unbindrule removes a rule from a column or from a user-defined datatype in the current database. If you do not want to unbind the rule from existing objname columns, use futureonly as the second parameter.

You cannot use sp_unbindrule to unbind a check constraint. Use alter table to drop the constraint.

To unbind a rule from a table column, specify the objname argument in the form “table.column”.

The rule is unbound from all existing columns of the user-defined datatype unless the rule has been changed or the value of the optional second parameter is futureonly.

To display the text of a rule, execute sp_helptext with the rule name as the parameter.

Permissions

Only the object owner can execute sp_unbindrule.

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
68	unbind	sp_unbindrule	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 in effect