SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


fn_trace_gettable Permissions Issue


fn_trace_gettable Permissions Issue

Author
Message
SqlRandall
SqlRandall
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 245
Hello all,

I've got a job that goes out to production servers and retrieves >1 sec queries from trace files generated the previous day(another job runs at midnight to kick off a server-side trace for this, with the server name and date embedded in the file name).

The long-and-short-of-it is, I can run the job from a server where the domain account is sysadmin. Unfortunately, the job must run from a server where the account cannot have sysadmin privs. That account IS sysadmin on the servers where the traces reside.

The select in the job is basically

INSERT TableName
SELECT Col 1,Col 2,......
FROM ::fn_trace_gettable( '\\ServerName\DirectoryPath\Filename', 0)



I've granted ALTER TRACE to the account, per BOL for that function, but to no avail. Any ideas?

Thanks in Advance,
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37075 Visits: 40280
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)



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SqlRandall
SqlRandall
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 245
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.
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37075 Visits: 40280
ok, still not an issue; read the file from the database, and recreate the view as needed:

--we want the current trace folder
declare @path nvarchar(256)
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
--you can select either the real default trace, or your specific trace.
SET @path = @path + N'LookitMyTrace' --system appends .trc automatically for the filename
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='LookitMyTrace ')
BEGIN
SET @sql = 'ALTER VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SqlRandall
SqlRandall
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 245
Lowell (3/3/2011)
ok, still not an issue; read the file from the database, and recreate the view as needed:

--we want the current trace folder
declare @path nvarchar(256)
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
--you can select either the real default trace, or your specific trace.
SET @path = @path + N'LookitMyTrace' --system appends .trc automatically for the filename
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='LookitMyTrace ')
BEGIN
SET @sql = 'ALTER VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW LookitMyTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END



Whoa! Interesting idea! I will try that.

Thanks!
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37075 Visits: 40280
SQLRandall did this idea work for you? I'm using a derivation of this technique to create a trace on the startup/restart of the server; i add a DML trace, and separately, a logon trace, and create/alter the views that i use to monitor them.

Was hoping it worked in more than my limited environment, where i query the views as a superuser, so probably miss any permissions issues.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27829 Visits: 17351
SqlRandall (3/3/2011)

I've granted ALTER TRACE to the account, per BOL for that function, but to no avail. Any ideas?

Thanks in Advance,

my guess is the domain account doesnt have permissions to read the windows file system where the trace files reside

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search