中繼資料可見性疑難排解
您可以使用本主題來疑難排解檢視中繼資料的問題。
使用者只能查看使用者擁有或被授與某些權限的中繼資料。此原則可防止擁有最低權限的使用者檢視 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_50.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