Troubleshooting Metadata Visibility

  • Article

Use this topic to troubleshoot problems viewing metadata.

In SQL Server 2005, a user can only see metadata that the user either owns or on which the user has been granted some permission. This policy prevents users with minimal privileges from viewing metadata for all objects in an instance of SQL Server 2005. For more information about metadata visibility, see Metadata Visibility Configuration.

To Let Users View Metadata

To let users with minimal privileges see all metadata, run one of the following statements:

  • GRANT VIEW ANY DEFINITION TO public;
    This statement will override metadata-visibility limitations at the instance level. All metadata in the instance will be visible to public.
  • GRANT VIEW DEFINITION TO public;
    This statement will override metadata-visibility limitations at the database level. All metadata in the database will be visible to public.
  • GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;
    This statement will override metadata-visibility limitations at the schema level. All metadata in the schema will be visible to public.
  • GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;
    This statement will override metadata-visibility limitations at the object level. All metadata for the object will be visible to public. If the object is a table, all the columns, indexes, statistics, and constraints of the table will be visible to the public. This behavior also applies to GRANT VIEW DEFINITION ON ASSEMBLY and other similar GRANT statements.

To enable a specific user with minimal privileges or a role to see all metadata, use a specific user or role name as the grantee instead of public.

To Let Users See Each Other

By default, users that have minimal privileges cannot see other users in the sys.database_principals and sys.server_principals catalog views. This means that a user with minimal privileges that owns a table cannot see other users to whom the user might want to grant permissions. To let user user_X with minimal privileges see another user, user_Y, you can issue the following GRANT statement:

  • GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>

You will have to run this statement for each user. You can automate the process by creating a DDL trigger similar to the following:

CREATE TRIGGER grant_view_definition_on_principal ON DATABASE
FOR CREATE_USER, CREATE_ROLE
AS
    DECLARE @event_type sysname, @principal_name sysname, @sql nvarchar(max);
    SELECT @event_type     = eventdata().value('(/EVENT_INSTANCE/EventType) [1]','sysname');
    SELECT @principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
    IF (@event_type = 'CREATE_USER')
        SELECT @sql = 'GRANT VIEW DEFINITION ON USER :: ' + @principal_name + ' TO PUBLIC ' ;
    ELSE
        SELECT @sql = 'GRANT VIEW DEFINITION ON ROLE :: ' + @principal_name + ' TO PUBLIC ' ;
    EXEC (@sql) ;
GO

To Let Application Roles See Server-Level Metadata

In SQL Server 2005, an application role cannot access metadata outside of its own database because application roles are not associated with a server-level principal. The following methods can be used to let application roles see server-level metadata.

Set a trace flag

To allow application roles to access server-level metadata, set global flag 4616. For information about setting trace flags, see DBCC TRACEON (Transact-SQL). For information about trace flag 4616, see Trace Flags (Transact-SQL).

Use a certificate-signed stored procedure

We recommend that you use certificate-signed procedures to access server-level system tables. Certificate-signed procedures offer the following benefits:

  • You do not have to use a trace flag.
  • Less server-level information may be disclosed. Application role-based applications must use stored procedures instead of general queries. Stored procedures are more likely to return only the specific data that is required by the application.
  • The following example creates a certificate-signed stored procedure and demonstrates how an application role can use the procedure to view server-level metadata.
USE master;
GO 
CREATE DATABASE approle_db; 
GO 
CREATE LOGIN some_login WITH PASSWORD = 'SomePa$$word!'; 
GO 
USE approle_db; 
GO 
CREATE USER some_user FOR LOGIN some_login; 
GO
EXEC sp_addapprole 'an_approle', 'SomeAppRolePa$$word!'; 
GO
--------------------------------------------------------------------- 
-- This section shows how to use a certificate to authenticate 
-- a signed procedure.
--------------------------------------------------------------------- 
CREATE LOGIN execute_as_login WITH PASSWORD = 'SomePa$$word!'; 
GO 
USE master; 
GO 
GRANT VIEW ANY DEFINITION TO execute_as_login; 
GRANT VIEW SERVER STATE TO execute_as_login; 
GO 
USE approle_db;
GO 
CREATE USER execute_as_user FOR LOGIN execute_as_login; 
GO 
--
-- You must use EXECUTE AS 'authenticator' here because the application role 
-- does not have a server identity. Therefore, the application role cannot use 
-- the certificate permissions on the server. Therefore, you 
-- need a new execution context to which you can grant 
-- the needed VIEW* permissions. 
-- 
CREATE PROC access_server_system_tables 
    WITH EXECUTE AS 'execute_as_user' 
    AS 
    SELECT sid, status, name, dbname, hasaccess, loginname 
        FROM master.dbo.syslogins; 
    SELECT spid, kpid, lastwaittype, waitresource, dbid 
        FROM master.dbo.sysprocesses; 
GO 
GRANT EXECUTE ON access_server_system_tables TO an_approle; 
GO 
CREATE CERTIFICATE signing_cert 
    ENCRYPTION BY PASSWORD = 'SomeCertPa$$word' 
    WITH SUBJECT = 'Signing Cert'; 
GO 
BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'; 
GO 
ADD SIGNATURE TO access_server_system_tables
    BY CERTIFICATE signing_cert WITH PASSWORD = 'SomeCertPa$$word';
GO
--------------------------------------------------------------------- 
-- Create a copy of the signing certificate in the target 
-- database. In this case, the target database is the master database. 
-- This copy of the signing certificate vouches for the execution context
-- that enters this database from the signed procedure. 
--------------------------------------------------------------------- 
USE master; 
GO 
CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'; 
GO 
--------------------------------------------------------------------- 
-- Because the VIEW permissions in question are server-level permissions,
-- we need to grant AUTHENTICATE SERVER permission on a login-mapped certificate. 
--------------------------------------------------------------------- 

CREATE LOGIN signing_cert_login FROM CERTIFICATE signing_cert; 
GO 
GRANT AUTHENTICATE SERVER TO signing_cert_login 
GO 
--------------------------------------------------------------------- 
-- Now you can open a new connection as "some_login" and 
-- set the application role. Then, call the "access_server_system_tables"
-- procedure, and obtain verification that you can access server-level information 
-- when the application role-based application runs. 
-- For an example, see the Demo usage.sql code below.
--------------------------------------------------------------------- 

--------------------------------------------------------------------- 
-- Clean up. 
-- The following statements remove the objects created above.
--------------------------------------------------------------------- 
USE master 
GO 
DROP DATABASE approle_db; 

DROP LOGIN some_login; 
GO 
DROP LOGIN execute_as_login; 
GO 
DROP LOGIN signing_cert_login; 
GO 
DROP CERTIFICATE signing_cert; 
GO 
-- 
-- Delete the certificate file. 
-- 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
RECONFIGURE; 
GO 
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer"'; 
GO 
EXEC sp_configure 'xp_cmdshell', 0; 
GO 
RECONFIGURE; 
GO 

-- ============================================================================
-- - Application role access to server information - Demo usage.sql
--
--  This code is companion code that shows an example of application role access
--  to server information by using a certificate-signed procedure.
--
-- ============================================================================
--  -------------------------------------------------- 
-- Connect as some_login first.
-- ------------------------------------------------ 
USE approle_db;
GO
EXEC sp_setapprole 'an_approle', 'SomeAppRolePa$$word!';
GO
-- Display the server-level information the application role can currently view. 
SELECT sid, status, name, dbname, hasaccess, loginname 
FROM master.dbo.syslogins; 
SELECT spid, kpid, lastwaittype, waitresource, dbid 
FROM master.dbo.sysprocesses; 
GO 
-- Display the server-level information the application role
-- can view by running the certificate-signed stored procedure.
EXEC access_server_system_tables;
GO

See Also

Tasks

Troubleshooting Metadata Visibility of Distributed Partitioned Views

Concepts

Metadata Visibility Configuration
Viewing Object Definitions
VIEW DEFINITION Permission
VIEW ANY DATABASE Permission

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Updated content:
  • Added information about using certificate-signed stored procedures in the section 'To Let Application Roles See Server-Level Metadata'.