元数据可见性故障排除
使用本主题可以解决查看元数据时出现的问题。
用户只能查看用户拥有的元数据或用户被授予其某些权限的元数据。此策略阻止拥有最低特权的用户查看 SQL Server 实例中所有对象的元数据。有关元数据可见性的详细信息，请参阅元数据可见性配置。
允许用户查看元数据
若要允许具有最低特权的用户查看所有元数据，请运行下列语句之一：
GRANT VIEW ANY DEFINITION TO public;
此语句将覆盖实例级的元数据可见性限制。实例中的所有元数据对 public 都将是可见的。
GRANT VIEW DEFINITION TO public;
此语句将覆盖数据库级的元数据可见性限制。数据库中的所有元数据对 public 都将是可见的。
GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;
此语句将覆盖架构级的元数据可见性限制。架构中的所有元数据对 public 都将是可见的。
GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;
此语句将覆盖对象级的元数据可见性限制。对象的所有元数据对 public 都将是可见的。如果对象是一个表，则表的所有列、索引、统计信息和约束对 public 都将是可见的。此行为也适用于 GRANT VIEW DEFINITION ON ASSEMBLY 和其他类似 GRANT 语句。
若要允许具有最低特权的特定用户或某个角色查看所有元数据，请使用具体的用户名或角色名作为被授权者，而不要使用 public。
允许用户彼此查看
默认情况下，具有最低特权的用户无法在 sys.database_principals 和 sys.server_principals 目录视图中看到其他用户。这意味着拥有表的最低特权用户无法查看他们可能想要授予其权限的其他用户。若要允许具有最低特权的用户 user_X 查看另一个用户 user_Y，可以运行以下 GRANT 语句：
- GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>
必须为每个用户运行此语句。可以通过创建与以下触发器类似的 DDL 触发器来自动完成此过程：
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
让应用程序角色看到服务器级元数据
应用程序角色无法访问其自身数据库外部的元数据，因为应用程序角色与服务器级主体不相关。应用程序角色可以使用下列方法查看服务器级元数据。
设置跟踪标志
若要允许应用程序角色访问服务器级元数据，请设置全局标志 4616。有关设置跟踪标志的信息，请参阅 DBCC TRACEON (Transact-SQL)。有关跟踪标志 4616 的信息，请参阅跟踪标志 (Transact-SQL)。
使用证书签名存储过程
建议使用证书签名过程访问服务器级系统表。证书签名过程具有下列优点：
不必使用跟踪标志。
可能公开较少的服务器级信息。基于应用程序角色的应用程序必须使用存储过程而不是常规查询。存储过程更可能仅返回应用程序所需的特定数据。
以下示例创建证书签名存储过程，并演示应用程序角色如何使用此过程查看服务器级元数据。
USE master;
GO
CREATE DATABASE approle_db;
GO
CREATE LOGIN some_login WITH PASSWORD = '<enterStrongPasswordHere>';
GO
USE approle_db;
GO
CREATE USER some_user FOR LOGIN some_login;
GO
EXEC sp_addapprole 'an_approle', '<enterStrongPasswordHere>';
GO
---------------------------------------------------------------------
-- This section shows how to use a certificate to authenticate
-- a signed procedure.
---------------------------------------------------------------------
CREATE LOGIN execute_as_login WITH PASSWORD = '<enterStrongPasswordHere>';
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 = '<enterStrongPasswordHere>'
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 = '<enterStrongPasswordHere>';
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\MSSQL10.MSSQLSERVER\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', '<enterStrongPasswordHere>';
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