sp_help and sp_depends display access rule information for columns. The Rule_name column of the output displays the domain rule attached to the column, and the Access_Rule_name column displays the name of the access rule.
For example, after adding the following access rules to the pubs2 database:
create access rule empl_id_access as @empid < "1111"
and then binding them to the publishers table:
sp_bindrule empl_id_access, "publishers.pub_id"
sp_help displays the the following access rules for the publishers table (this example only displays the pertinent information for space reasons):
1> sp_help publishers . . . Name Owner Object_type ----------------- -------------- ----------- publishers dbo user table Data_located_on_segment When_created ------------------------------ ------------------- default Jun 8 2001 10:05AM Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity--------------- --------------- ----------- ---- ----- ----- --------------- --------------- ------------------------------ -------- pub_id char 4 NULL NULL 0 NULL pub_idrule empl_id_access 0 pub_name varchar 40 NULL NULL 1 NULL NULL NULL . . .
If you issue sp_help with only the access rule name, it describes if it an OR or an AND access rule:
sp_help name_access Name Owner Object_type -------------- ------- -------------- empl_id_access dbo AND accessrule Data_located_on_segment When_created ------------------------- -------------------- not applicable Jun 12 2001 4:18PM
name_access access rule is an OR access rule in the publishers table.
sp_depends displays the dependencies for the publishers table, including the access rules bound to this table:
1> sp_depends publishersObject doesn't reference any object and no objects reference it. Dependent objects that reference all columns in the table. Use sp_depends on each column to get more information. Columns referenced in stored procedures, views or triggers are not included in this report. Column Type Object Names or Column Names ----------------- ----------- ---------------------------- pub_id accessrule empl_id_access pub_id index pubind (pub_id) pub_id logical RI From titles (pub_id) To publishers (pub_id) pub_id logical RI publishers (pub_id) pub_id rule pub_idrule pub_id statistics (pub_id)
