SQL Server EXECUTE AS on FN_TRACE_GETTABLE

Question

I am trying to allow a user to view SQL Server trace data from a .trc file without giving them ALTER TRACE permission (SQL Server 2008 R2). So I wrapped it in a stored procedure using my sysadmin account:

 CREATE PROCEDURE test_trace as SELECT * FROM FN_TRACE_GETTABLE(N'C:\temp\trace1.trc', 1) 

If I run this stored procedure using my sysadmin account, it works fine as expected. If I try to run this under the account domain1 \ user1, it does not start with the error message "You do not have permission to run 'FN_TRACE_GETTABLE'" . This is expected again.

So now I want domain1\user1 run the stored procedure, so I change the stored procedure to run under the sysadmin account:

 CREATE PROCEDURE test_trace WITH EXECUTE AS 'domain1\sysadmin1' as SELECT * FROM FN_TRACE_GETTABLE(N'C:\temp\trace1.trc', 1) 

Now, when I execute the stored procedure, I get "You do not have permission to run 'FN_TRACE_GETTABLE'" regardless of the account I execute in! I expected that I could execute it both under the accounts domain1\user1 , and domain1\sysadmin1 .

Can someone help with what I missed? My goal is to allow domain1\user1 to read trace1.trc without giving them ALTER TRACE permission.

Answer 1

You need to use code signing to elevate privileges in a controlled manner. Although you are isolated in the context of the EXECUTE AS procedure and cannot use the privilege at the server level (for example, trace-related permissions), read Extending Database Impersonation with EXECUTE AS . Signing code is the right solution to this problem. See the full example here .

Answer 2

According to the documentation, you can only specify the username (database) in execute as for stored procedures. However, I think this should work fine, but please change the SP using the sysadmin account and specify EXECUTE AS SELF instead of the username.

If this still does not work, try granting the executing user read permissions in the trace file, perhaps the server ignores execute as to access the file (which I consider an error).