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.