SQL Serverは、証明書の最小粒度認証を使用します.

最近、プロジェクト内のある機能で、ブロックによるTime outエラーが発生しました.そのため、この機能を実行するときに、スレッド検出SQLサーバでセッションをブロックする必要があります.ただし、プログラム自体が使用するsqlアカウント自体はVIEW Sever State権限を備えていない.この権限はsys_adminキャラクターの.そのため、証明書を使用してViewServer State権限を通常のアカウントに付与する必要があります.
開発前に以下の2つのブログの参考を見つけました.
SQL Server 2005:ストアド・プロシージャ署名-stswordman-ブログ・パークhttp://www.cnblogs.com/stswordman/archive/2008/07/29/1255322.html
この記事ではmaster keyを使用しています.データベースのバックアップ後、他のデータベースを復元するにはmaster keyが必要です.
Pitching Pebbles - Using Certificates to Grant Privileges | ColleenMorrow.com http://colleenmorrow.com/2011/12/19/tossing-pebbles-using-certificates-to-grant-privileges/
この1篇の直接使用する証明書の鍵はします.前の1篇のmaster keyがもたらした問題がありません.ただ例はmasterライブラリを使用します.および直接与えたsys_adminロールの権限
 
私は2つのブログの要点を結びつけて、証明書のパスワードの方式を採用してView Server Stateの権限を授与します.
 
以下はコードです
ブロッキング・プロセスを表示するストレージ・プロシージャを新規作成します.

USE [TestDB]
GO

CREATE PROC [dbo].[sp_CheckBlockSession]
AS
WITH    CTE_SID ( BSID, SID, sql_handle )
          AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.SESSION_ID = B.BSID
             )
    SELECT  C.BSID AS blocking_session_id,
            C.SID AS session_id,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM    CTE_SID C 
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid

最小権限アカウントを新規作成

USE TestDB
GO
--          
CREATE LOGIN TEST_View WITH PASSWORD = '123';
CREATE USER TEST_View FOR LOGIN Test_View
--             dbo.sp_CheckBlockSession
GRANT EXECUTE ON sp_CheckBlockSession TO Test_View

最小権限アカウントのテストsp_CheckBlockSession

EXECUTE AS LOGIN = 'TEST_View' ;
EXEC sp_CheckBlockSession 
REVERT ;
 --    
    297，   16，   1，   sp_CheckBlockSession，  3  
            。

 

USE TestDB
GO
--        
CREATE CERTIFICATE certSignCheckBlockSession_1 ENCRYPTION BY PASSWORD ='TEST111' WITH SUBJECT = 'for signing procedure sp_CheckBlockSession', EXPIRY_DATE='2025-10-10' ;
-- sp_CheckBlockSession
ADD SIGNATURE TO sp_CheckBlockSession BY CERTIFICATE certSignCheckBlockSession_1 WITH PASSWORD = 'TEST111' ;
BACKUP CERTIFICATE certSignCheckBlockSession_1 TO FILE = 'certSignCheckBlockSession_1.cer' ;

USE master
GO
CREATE CERTIFICATE certSignCheckBlockSession_1 FROM FILE = 'certSignCheckBlockSession_1.cer' ;
 
--           
CREATE LOGIN L_TESTVIEW FROM CERTIFICATE certSignCheckBlockSession_1 ;
 --             ALTER ANY LOGIN      
GRANT VIEW SERVER STATE TO L_TESTVIEW ;

 

--          
USE TestDB
GO
EXECUTE AS LOGIN = 'TEST_View' ;
EXEC sp_CheckBlockSession 
REVERT ;--      ,              .

以上が証明書署名を用いた記憶プロセスの全プロセスであり,最小粒度権限の付与を実現できる.これにより、生産環境において不要な管理権限を付与することが回避される.リスクを軽減!
 注意事項:証明書バックアップパスの問題.絶対パスが指定されていない場合.バックアップファイルはD:Program Files(x 86)Microsoft SQL ServerMSSQL 10_50.MSSQLSERVERMSSQLDATA(ビル主のマシン)ディレクトリの下.このディレクトリは権限が不足してバックアップできない可能性があります.もともと絶対パスを使いたかったのですが.しかし、あるプロジェクトを開発する際に、機能を使用したため、現在のアカウントの権限は小さく縮小されました.絶対パスを使用してバックアップできないため、デフォルトのパスが使用されます.皆さんがテストをするときはこの問題に注意してください.