Metadata visibility configuration only applies to the following securables.

Metadata visibility configuration does not apply to the following securables.

Limited metadata accessibility means the following:

• Applications that assume public metadata access will break.
  
• Queries on system views might only return a subset of rows, or sometimes an empty result set.
  
• Metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return NULL.
  
• The Database Engine sp_help stored procedures might return only a subset of rows, or NULL.
  

SQL modules, such as stored procedures and triggers, run under the security context of the caller and, therefore, have limited metadata accessibility. For example, in the following code, when the stored procedure tries to access metadata for the table myTable on which the caller has no rights, an empty result set is returned. In earlier releases of SQL Server, a row is returned.

CREATE PROCEDURE assumes_caller_can_access_metadata
BEGIN
SELECT name, id 
FROM sysobjects 
WHERE name = 'myTable';
END;
GO

To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level or server level. Therefore, in the previous example, if the caller has VIEW DEFINITION permission on myTable, the stored procedure returns a row. For more information, see GRANT (Transact-SQL) and GRANT Database Permissions (Transact-SQL).

You can also modify the stored procedure so that it executes under the credentials of the owner. When the procedure owner and the table owner are the same owner, ownership chaining applies, and the security context of the procedure owner enables access to the metadata for myTable. Under this scenario, the following code returns a row of metadata to the caller.

Note:
The following example uses the sys.objects catalog view instead of the sys.sysobjects compatibility view.

CREATE PROCEDURE does_not_assume_caller_can_access_metadata
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT name, id
FROM sys.objects 
WHERE name = 'myTable' 
END;
GO

Note:
You can use EXECUTE AS to temporarily switch to the security context of the caller. For more information, see EXECUTE AS (Transact-SQL).

For more information about ownership chaining, see Ownership Chains.

Metadata visibility configuration can play an important role in your overall security plan. However, there are cases in which a skilled and determined user can force the disclosure of some metadata. We recommend that you deploy metadata permissions as one of many defenses-in-depth.

It is theoretically possible to force the emission of metadata in error messages by manipulating the order of predicate evaluation in queries. The possibility of such trial-and-error attacks is not specific to SQL Server. It is implied by the associative and commutative transformations permitted in relational algebra. You can mitigate this risk by limiting the information returned in error messages. To further restrict the visibility of metadata in this way, you can start the server with trace flag 3625. This trace flag limits the amount of information shown in error messages. In turn, this helps to prevent forced disclosures. The tradeoff is that error messages will be terse and might be difficult to use for debugging purposes. For more information, see Using the SQL Server Service Startup Options and Trace Flags (Transact-SQL).

The following metadata is not subject to forced disclosure:

• The value stored in the provider_string column of sys.servers. A user that does not have ALTER ANY LINKED SERVER permission will see a NULL value in this column.
  
• Source definition of a user-defined object such as a stored procedure or trigger. The source code is visible only when one of the following is true:
  
  • The user has VIEW DEFINITION permission on the object.
    
  • The user has not been denied VIEW DEFINITION permission on the object and has CONTROL, ALTER, or TAKE OWNERSHIP permission on the object. All other users will see NULL.
    
• The definition columns found in the following catalog views:
  

  sys.all_sql_modules	sys.sql_modules
  sys.server_sql_modules	sys.check_constraints
  sys.default_constraints	sys.computed_columns
  sys.numbered_procedures

• The ctext column in the syscomments compatibility view.
  
• The output of the sp_helptext procedure.
  
• The following columns in the information schema views:
  

  INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE	INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT
  INFORMATION_SCHEMA.DOMAINS.DOMAIN_DEFAULT	INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULT
  INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION	INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION

• OBJECT_DEFINITION() function
  
• The value stored in the password_hash column of sys.sql_logins. A user that does not have CONTROL SERVER permission will see a NULL value in this column.
  

Note:
The SQL definitions of built-in system procedures and functions are publicly visible through the sys.system_sql_modules catalog view, the sp_helptext stored procedure, and the OBJECT_DEFINITION() function.

The following are some general principles to consider regarding metadata visibility:

• Fixed roles implicit permissions
  
• Scope of permissions
  
• Precedence of DENY
  
• Visibility of subcomponent metadata
  

Fixed Roles and Implicit Permissions

Scope of Permissions

Precedence of DENY

Visibility of Subcomponent Metadata

Concepts

Other Resources

Help and Information