September 23, 2009 at 8:14 am
I want to track any activity by a user's computer regardless of how they log on. This person has "view definition" rights, but does have a different logons available to him. Can I track all his logons and activities based on his computer name?
September 23, 2009 at 8:28 am
if you add a server side trace, then you can use the HOSTNAME column, which resolves to the computername that connected, in the trace to track everything from that PC.
you have to add the trace, and from that point on, you can track that hostname's activities.
the information is not discoverable with out it, so you can't see what they did in the past, prior to adding the trace.
here's the trace i typically use and leave running at all times.
declare @sql varchar(1000)
declare @path nvarchar(256)
declare @traceidout int
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @on bit
set @on = 1
set @maxRolloverFiles = 2
set @maxfilesize = 50
--we want the current trace folder
--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
SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename
--create the trace
exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL
--for the Event Every SQL statement completed, capture all 64 columns of accessible data
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID
exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber
exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName
exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName
exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID
exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName
exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName
exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID
exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration
exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime
exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime
exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads
exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes
exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU
exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions
exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity
exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass
exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID
exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success
exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID
exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData
exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName
exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass
exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType
exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel
exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State
exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error
exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode
exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle
exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName
exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName
exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName
exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName
exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName
exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName
exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName
exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid
exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName
exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid
exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions
exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName
exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName
exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName
exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts
exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID
exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence
exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence
exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1
exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2
exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID
exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2
exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2
exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type
exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID
exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName
exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem
exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset
exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID
exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle
exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName
--adding RPC calls: When an application executes a stored procedure, only a simple,
--small RPC (remote procedure call) is made from the client to SQL Server.
exec sp_trace_setevent @traceidout, 10, 1, @on --RPC:Completed,TextData
exec sp_trace_setevent @traceidout, 10, 2, @on --RPC:Completed,BinaryData
exec sp_trace_setevent @traceidout, 10, 3, @on --RPC:Completed,DatabaseID
exec sp_trace_setevent @traceidout, 10, 4, @on --RPC:Completed,TransactionID
exec sp_trace_setevent @traceidout, 10, 5, @on --RPC:Completed,LineNumber
exec sp_trace_setevent @traceidout, 10, 6, @on --RPC:Completed,NTUserName
exec sp_trace_setevent @traceidout, 10, 7, @on --RPC:Completed,NTDomainName
exec sp_trace_setevent @traceidout, 10, 8, @on --RPC:Completed,HostName
exec sp_trace_setevent @traceidout, 10, 9, @on --RPC:Completed, ClientProcessID
exec sp_trace_setevent @traceidout, 10,10, @on --RPC:Completed,ApplicationName
exec sp_trace_setevent @traceidout, 10,11, @on --RPC:Completed,LoginName
exec sp_trace_setevent @traceidout, 10,12, @on --RPC:Completed,SPID
exec sp_trace_setevent @traceidout, 10,13, @on --RPC:Completed,Duration
exec sp_trace_setevent @traceidout, 10,14, @on --RPC:Completed,StartTime
exec sp_trace_setevent @traceidout, 10,15, @on --RPC:Completed,EndTime
exec sp_trace_setevent @traceidout, 10,16, @on --RPC:Completed,Reads
exec sp_trace_setevent @traceidout, 10,17, @on --RPC:Completed,Writes
exec sp_trace_setevent @traceidout, 10,18, @on --RPC:Completed,CPU
exec sp_trace_setevent @traceidout, 10,19, @on --RPC:Completed,Permissions
exec sp_trace_setevent @traceidout, 10,20, @on --RPC:Completed,Severity
exec sp_trace_setevent @traceidout, 10,21, @on --RPC:Completed,EventSubClass
exec sp_trace_setevent @traceidout, 10,22, @on --RPC:Completed,ObjectID
exec sp_trace_setevent @traceidout, 10,23, @on --RPC:Completed,Success
exec sp_trace_setevent @traceidout, 10,24, @on --RPC:Completed,IndexID
exec sp_trace_setevent @traceidout, 10,25, @on --RPC:Completed,IntegerData
exec sp_trace_setevent @traceidout, 10,26, @on --RPC:Completed,ServerName
exec sp_trace_setevent @traceidout, 10,27, @on --RPC:Completed,EventClass
exec sp_trace_setevent @traceidout, 10,28, @on --RPC:Completed,ObjectType
exec sp_trace_setevent @traceidout, 10,29, @on --RPC:Completed,NestLevel
exec sp_trace_setevent @traceidout, 10,30, @on --RPC:Completed,State
exec sp_trace_setevent @traceidout, 10,31, @on --RPC:Completed,Error
exec sp_trace_setevent @traceidout, 10,32, @on --RPC:Completed,Mode
exec sp_trace_setevent @traceidout, 10,33, @on --RPC:Completed,Handle
exec sp_trace_setevent @traceidout, 10,34, @on --RPC:Completed,ObjectName
exec sp_trace_setevent @traceidout, 10,35, @on --RPC:Completed,DatabaseName
exec sp_trace_setevent @traceidout, 10,36, @on --RPC:Completed,FileName
exec sp_trace_setevent @traceidout, 10,37, @on --RPC:Completed,OwnerName
exec sp_trace_setevent @traceidout, 10,38, @on --RPC:Completed,RoleName
exec sp_trace_setevent @traceidout, 10,39, @on --RPC:Completed,TargetUserName
exec sp_trace_setevent @traceidout, 10,40, @on --RPC:Completed,DBUserName
exec sp_trace_setevent @traceidout, 10,41, @on --RPC:Completed,LoginSid
exec sp_trace_setevent @traceidout, 10,42, @on --RPC:Completed,TargetLoginName
exec sp_trace_setevent @traceidout, 10,43, @on --RPC:Completed,TargetLoginSid
exec sp_trace_setevent @traceidout, 10,44, @on --RPC:Completed,ColumnPermissions
exec sp_trace_setevent @traceidout, 10,45, @on --RPC:Completed,LinkedServerName
exec sp_trace_setevent @traceidout, 10,46, @on --RPC:Completed,ProviderName
exec sp_trace_setevent @traceidout, 10,47, @on --RPC:Completed,MethodName
exec sp_trace_setevent @traceidout, 10,48, @on --RPC:Completed,RowCounts
exec sp_trace_setevent @traceidout, 10,49, @on --RPC:Completed,RequestID
exec sp_trace_setevent @traceidout, 10,50, @on --RPC:Completed,XactSequence
exec sp_trace_setevent @traceidout, 10,51, @on --RPC:Completed,EventSequence
exec sp_trace_setevent @traceidout, 10,52, @on --RPC:Completed,BigintData1
exec sp_trace_setevent @traceidout, 10,53, @on --RPC:Completed,BigintData2
exec sp_trace_setevent @traceidout, 10,54, @on --RPC:Completed,GUID
exec sp_trace_setevent @traceidout, 10,55, @on --RPC:Completed,IntegerData2
exec sp_trace_setevent @traceidout, 10,56, @on --RPC:Completed,ObjectID2
exec sp_trace_setevent @traceidout, 10,57, @on --RPC:Completed,Type
exec sp_trace_setevent @traceidout, 10,58, @on --RPC:Completed,OwnerID
exec sp_trace_setevent @traceidout, 10,59, @on --RPC:Completed,ParentName
exec sp_trace_setevent @traceidout, 10,60, @on --RPC:Completed,IsSystem
exec sp_trace_setevent @traceidout, 10,61, @on --RPC:Completed,Offset
exec sp_trace_setevent @traceidout, 10,62, @on --RPC:Completed,SourceDatabaseID
exec sp_trace_setevent @traceidout, 10,63, @on --RPC:Completed,SqlHandle
exec sp_trace_setevent @traceidout, 10,64, @on --RPC:Completed,SessionLoginName
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply