Grant User permission to view XE results via sys.fn_xe_file_target_read_file

Question

I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file

I thought the best way to do this would be to encapsulate the logic within a stored procedure in a database which John Smith uses called MyDb and then grant him access to the stored procedure.

I have solved this so far but it means using impersonation of the dbo user within the stored procedure and setting IS_TRUSTWORTHY on which some Googling suggests is a less secure method and that signing a stored procedure is the most secure way to achieve this.

Going the the signing method, I have hit some problems as below:

First of all, I create a certificate in the database:

USE MyDb
GO

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

then I create my stored procedure which has the logic

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

Next, I sign the stored procedure

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

Then I create a user which uses the certificate

CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; 

At this point, I need to grant the underlying privilege that the is required to run sys.fn_xe_file_target_read_file which is GRANT VIEW SERVER STATE

GRANT VIEW SERVER STATE TO [CodeSigningUser]

but when I try to do this, I get

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

which makes perfect sense as it is a server level permission that I am trying to grant to a database level principal

I have tried creating a login for the certificate (as I can grant VIEW SERVER STATE to a login) :

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate]; 

but I get the error

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

Which, again makes sense as the certificate is in a user database

I can create the certificate in the master database which allows me to create the login from the certificate but then I can't sign the stored procedure using that certificate as the certificate resides in a different database to the stored procedure.

The only way I can think of to accomplish this is to create the Stored Procedure in the master database and then create the certificate, also in the master database, create the login and assign VIEW SERVER STATE to the login.

Is there a way I can keep my stored procedure in MyDb and have a user MyDomain\JohnSmith execute it to be able to see the XE Session Results?

Answer 1

I have managed to do this now thanks to this article the missing piece of the puzzle was to copy the certificate to the master database

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

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