SQL Server使用证书最小粒度授权.

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

EXECUTE AS LOGIN = ‘TEST_View‘ ;
EXEC sp_CheckBlockSession 
REVERT ;
 --错误消息
 消息 297，级别 16，状态 1，过程 sp_CheckBlockSession，第 3 行
用户没有执行此操作的权限。

USE TestDB
GO
--创建证书以及授权
CREATE CERTIFICATE certSignCheckBlockSession_1 WITH SUBJECT = ‘for signing procedure sp_CheckBlockSession‘ ;
-- sp_CheckBlockSession
ADD SIGNATURE TO sp_CheckBlockSession BY CERTIFICATE certSignCheckBlockSession_1 ;
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 ;

--成功得到结果, 不再提示用户没有此操作权限.