Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

fn_trace_gettable Permissions Issue Expand / Collapse
Author
Message
Posted Thursday, March 3, 2011 12:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 1:56 PM
Points: 13, Visits: 240
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,
Post #1072850
Posted Thursday, March 3, 2011 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,955, Visits: 32,487
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1072876
Posted Thursday, March 3, 2011 2:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 1:56 PM
Points: 13, Visits: 240
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.
Post #1072921
Posted Thursday, March 3, 2011 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,955, Visits: 32,487
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1072953
Posted Thursday, March 3, 2011 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 1:56 PM
Points: 13, Visits: 240
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!
Post #1072956
Posted Friday, March 4, 2011 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,955, Visits: 32,487
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1073415
Posted Sunday, March 6, 2011 12:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 6,751, Visits: 14,390
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"
Post #1073897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse