sp_unbindrule

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

Syntax

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

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.

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:
```
sp_unbindrule "empl_access", NULL, "accessrule"
```

There are additional considerations when using sp_unbindrule:

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.

See also create rule, drop rule in Reference Manual: Commands.

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

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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