I need to grant a user (
MyDomain\JohnSmith) permission to view one particular XE session results using
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)', 'NVARCHAR(MAX)' ) AS [TimeStamp], CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)', 'NVARCHAR(MAX)' ) AS [Database_name], CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)', 'NVARCHAR(MAX)' ) AS [Statement], CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)', 'NVARCHAR(MAX)' ) AS SQL_Text, CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)', 'NVARCHAR(MAX)' ) AS Username, CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)', 'NVARCHAR(MAX)' ) AS Client_Hostname, CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)', '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?