locked
INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE - make it visible to users who is not a schema owner RRS feed

  • Question

  • INFORMATION_SCHEMA views are visible to users. However, when SELECT *  INFORMATION_SCHEMA.CHECK_CONSTRAINTS is executed, only schema owner of the object can view the CHECK_CLAUSE column. Users who are not the schema owner can see NULL in the column.

    The outcome is the same even VIEW DEFINITION is granted on schema::INFORMATION_SCHEMA is granted to the user.

    Same result in  sys.check_constrains.definition.

    I read an article on metadata visibility. But, not clear if there is a way to make the column visible to non-schema owner users.

    Anyone knows how to do it?

    Thursday, January 25, 2018 5:49 PM

All replies

  • Hi IndigoGal,

    As mentioned in this document: Metadata Visibility Configuration, there are some limits of Metadata Visibility Configuration, INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE is included in the list. The definition columns found in the view sys.check_constraints is also not suitable for Metadata Visibility Configuration.

    Because these views are limited to securables that a user either owns or on which the user has been granted some permission and the above conditions are excluded, we can only use the owner user to access it.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Teige Gao Wednesday, January 31, 2018 2:22 AM
    Friday, January 26, 2018 8:26 AM
  • I don't know if it changes things, but what about grant VIEW DEFINITION to the object (table) in question?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, January 26, 2018 8:30 AM
  • I granted VIEW DEFINITION to the user in the database:

    GRANT VIEW DEFINITION ON schema::INFORMATION_SCHEMA to <user>

    But, it did not change.

    (I could not grant VIEW DEFINITION to INFORMATION_SCHEMA.CHECK_CONSTRAINTS to <user>. I got an error message, it can only allow in master database. I mapped this login to master database and granted. But, it did not change anything.

    Friday, January 26, 2018 3:55 PM
  • I didn't ask you to grant view definition on the info schema view. I asked you to grant view definition on the *table on which the check constraint created*. GRAT VIEW DEFINITION ON theSchema.TheTable TO <user>.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, January 26, 2018 5:55 PM