[アーティクル]
10/25/2012

メタデータ表示のトラブルシューティング

このトピックでは、メタデータの表示に関する問題のトラブルシューティングを行います。

ユーザーは、ユーザーが所有しているか権限を許可されているメタデータのみを表示できます。そのため、最小限の特権しかないユーザーは、SQL Server のインスタンス内のすべてのオブジェクトのメタデータを表示できません。メタデータ表示の詳細については、「メタデータ表示の構成」を参照してください。

ユーザーがメタデータを表示できるようにするには

最小限の特権しかないユーザーがすべてのメタデータを表示できるようにするには、次のステートメントのいずれかを実行します。

GRANT VIEW ANY DEFINITION TO public;

このステートメントでは、インスタンスレベルでメタデータの表示制限が無効になります。インスタンス内のすべてのメタデータをパブリックに表示できるようになります。
GRANT VIEW DEFINITION TO public;

このステートメントでは、データベースレベルでメタデータの表示制限が無効になります。データベース内のすべてのメタデータをパブリックに表示できるようになります。
GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;

このステートメントでは、スキーマレベルでメタデータの表示制限が無効になります。スキーマ内のすべてのメタデータをパブリックに表示できるようになります。
GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;

このステートメントでは、オブジェクトレベルでメタデータの表示制限が無効になります。オブジェクト内のすべてのメタデータをパブリックに表示できるようになります。オブジェクトがテーブルだとすると、そのテーブル内のすべての列、インデックス、統計情報、および制約をパブリックに表示できるようになります。また、この動作は GRANT VIEW DEFINITION ON ASSEMBLY などの GRANT ステートメントにも適用されます。

最小限の特権しかない特定のユーザーまたはあるロールがすべてのメタデータを表示できるようにするには、権限が許可されているユーザーとしてパブリックではなく、特定のユーザー名またはロール名を指定します。

ユーザーを相互に見えるようにするには

既定では、最小限の特権しかないユーザーからは、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

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

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

次の方法で共有

メタデータ表示のトラブルシューティング

ユーザーがメタデータを表示できるようにするには

ユーザーを相互に見えるようにするには

アプリケーションロールでサーバーレベルのメタデータを表示するには

関連項目

タスク

概念

その他のリソース

次の方法で共有

メタデータ表示のトラブルシューティング

ユーザーがメタデータを表示できるようにするには

ユーザーを相互に見えるようにするには

アプリケーション ロールでサーバー レベルのメタデータを表示するには

関連項目

タスク

概念

その他のリソース

アプリケーションロールでサーバーレベルのメタデータを表示するには