Lowell (3/3/2011)
what i would suggest do is on the server itself, create a VIEW that selects the data from the trace; the view would run under the context of the owner of the view, right? then I believe you can easily grant SELECT permissions to a role or user. does that sound right?something like this, which is selecting my default trace files from a default SQL 2008 installation:
CREATE VIEW vw_LookitMyTrace
AS
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_10.trc', default)
Thanks for replying. The view idea sounds interesting, but one issue right off the bat is, the name of the trace file changes daily(the date is embedded in the file name).
One other workaround would be to set up a job on each of the production servers to insert the data into the table on the local server. The non-admin account could then just select from those tables via linked server connections(works, I just verified). I was just hoping to avoid having to set up those intermediate steps on the production boxes.