Unbinds a rule from a column or from a user-defined datatype.
sp_unbindrule objname [, futureonly [, “accessrule” | “all”]]
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.
prevents columns of the specified user-defined datatype from losing their rules. It is ignored when unbinding a rule from a column.
indicates that you are unbinding the access rule bound to objname.
specifies that you are unbinding all rules bound to objname.
Unbinds the rule from the startdate column of the employees table:
sp_unbindrule "employees.startdate"
Unbinds the rule from the user-defined datatype named def_ssn and all columns of that type:
sp_unbindrule def_ssn
The user-defined datatype ssn no longer has a rule, but existing ssn columns are unaffected:
sp_unbindrule ssn, futureonly
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"
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"
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.
Only the object owner can execute sp_unbindrule.
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 |
|
Commands create rule, drop rule
System procedures sp_bindrule, sp_helptext