Shawn Smith (2/19/2013)

---

Great ideas but unfortunately they did not resolve my particular issue. After running with your initial advice with no luck I tried extending your DENY commands to sys.[default_constraints], sys.[key_constraints], sys.[foreign_keys], and dbo.[sysconstraints]. I know this was grasping at straws but my thinking here was the sys schema views replace the old dbo sysconstraints table so why not try to deny on everything related?

In the end, I'm still stuck with these INFORMATION_SCHEMA objects reporting (but in effect not having) SELECT permissions to the public role:

CHECK_CONSTRAINTS

COLUMN_DOMAIN_USAGE

COLUMN_PRIVILEGES

COLUMNS

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DOMAIN_CONSTRAINTS

DOMAINS

KEY_COLUMN_USAGE

REFERENTIAL_CONSTRAINTS

SCHEMATA

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TABLES

VIEW_COLUMN_USAGE

VIEW_TABLE_USAGE

VIEWS

One curious thing is that these three other INFORMATION_SCHEMA objects are reporting correctly for me:

PARAMETERS

ROUTINE_COLUMNS

ROUTINES

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'check_constraints', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line 4

The SELECT permission was denied on the object 'CHECK_CONSTRAINTS', database 'mssqlsystemresource', schema 'INFORMATION_SCHEMA'.