Viewing Object Definitions

The rules for viewing the Transact-SQL source code of database objects are stricter than for viewing metadata. To see the Transact-SQL definition of an object, a user must either own the object or have been granted one of the following permissions on the object:

  • CONTROL

  • ALTER

  • TAKE OWNERSHIP

  • VIEW DEFINITION

The following are some examples of how these rules work:

  • If a user is granted ALTER permission on a table myTable, the user can see the Transact-SQL definition of the subcomponents that are related to the table in the definition column in each of the catalog views that are listed in the following table. If a user does not have one of the previous permissions or does not own the object, the Transact-SQL definition in the definition column of the catalog view is NULL.

  • If the user is not the table owner of myTable and has been granted only SELECT permission on the table, when the user accesses the following catalog views, the definition columns are NULL.

    Catalog view name

    Transact-SQL definitions for

    sys.sql_modules

    All triggers in mytable

    sys.computed_columns

    All computed column in myTable

    sys.check_constraints

    All CHECK constraints in myTable

    sys.default_constraints

    All DEFAULT constraints in myTable

  • Assume that a user is granted EXECUTE permission on the procedure myProcedure. When the user tries to see the Transact-SQL definition of the procedure by accessing the sys.sql_modules catalog view, the definition column has a null value. Conversely, if the user has been granted TAKE OWNERSHIP permission on myProcedure, the user will see the Transact-SQL definition of the procedure in the definition column of sys.sql_modules.