March 3, 2011 at 12:52 pm
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,
March 3, 2011 at 1:08 pm
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
March 3, 2011 at 2:16 pm
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.
March 3, 2011 at 2:43 pm
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
March 3, 2011 at 2:47 pm
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!
March 4, 2011 at 9:25 am
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
March 6, 2011 at 12:59 pm
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" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply