通過 sys.fn_xe_file_target_read_file 授予使用者查看 XE 結果的權限

我需要授予使用者 ( MyDomain\JohnSmith) 權限以查看一個特定的 XE 會話結果，使用sys.fn_xe_file_target_read_file

我認為最好的方法是將邏輯封裝在 John Smith 使用的數據庫中的儲存過程中MyDb，然後授予他對儲存過程的訪問權限。

到目前為止我已經解決了這個問題，但這意味著在儲存過程中使用 dbo 使用者的模擬，IS_TRUSTWORTHY並且一些Google搜尋建議的設置是一種不太安全的方法，並且簽署儲存過程是實現這一點的最安全的方法。

採用簽名方法，我遇到了一些問題，如下所示：

首先，我在數據庫中創建一個證書：

USE MyDb
GO

CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH EXPIRY_DATE = '2099-01-01',
SUBJECT = 'Code Signing Cert'

然後我創建具有邏輯的儲存過程

CREATE PROCEDURE MySchema.MyProc
AS
BEGIN

   DECLARE @TraceFilePath NVARCHAR(256) = 'Path/To/My/File*.xel'

   SELECT  CONVERT(XML,event_data).value('(/event/@timestamp)[1]', 'NVARCHAR(MAX)' ) AS [TimeStamp],
           CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Database_name],
           CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Statement],
           CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)' ) AS SQL_Text,
           CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)[1]', 'NVARCHAR(MAX)' ) AS Username,
           CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_Hostname,
           CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_app_name
   INTO    #Results
   FROM    sys.fn_xe_file_target_read_file (@TraceFilePath, NULL, NULL, NULL )


   SELECT  *
   FROM    #Results
   WHERE   Statement LIKE '%some value%'
   ORDER BY SQL_Text
END

接下來，我簽署儲存過程

ADD SIGNATURE TO MySchema.MyProc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = 'SuperSecretPassword';

然後我創建一個使用證書的使用者

CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; 

此時，我需要授予執行所需的基礎特權，sys.fn_xe_file_target_read_file即GRANT VIEW SERVER STATE

GRANT VIEW SERVER STATE TO [CodeSigningUser]

但是當我嘗試這樣做時，我得到

"Msg 4621, Level 16, State 10, Line 44
Permissions at the server scope can only be granted when the current database is master"

這很有意義，因為它是我試圖授予數據庫級別主體的伺服器級別權限

我嘗試為證書創建登錄名（因為我可以授予VIEW SERVER STATE登錄名）：

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate]; 

但我得到了錯誤

Msg 15151, Level 16, State 1, Line 40
Cannot find the certificate 'CodeSigningCertificate', because it does not exist or you do not have permission.

這又是有意義的，因為證書在使用者數據庫中

我可以在主數據庫中創建證書，它允許我從證書創建登錄名，但是我不能使用該證書對儲存過程進行簽名，因為證書與儲存過程位於不同的數據庫中。

我能想到的唯一方法是在 master 數據庫中創建儲存過程，然後在 master 數據庫中創建證書，創建登錄名並將 VIEW SERVER STATE 分配給登錄名。

有沒有辦法可以將儲存過程保存在 MyDb 中並讓使用者 MyDomain\JohnSmith 執行它以查看 XE 會話結果？

多虧了這篇文章，我現在已經做到了，缺少的部分是將證書複製到主數據庫

DECLARE @Cert NVARCHAR(4000) =
        CONVERT(NVARCHAR(4000),
                CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                1);

EXEC (N'USE [master];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);

引用自：https://dba.stackexchange.com/questions/249939