June 25, 2009 at 10:15 am
Even though only a few people have access to the new payroll database, I'd be interested in knowing if SQL Server 2005 has a way to keep track of, say, if someone opened SSMS and viewed the table contents that way. Is this possible?
June 25, 2009 at 10:19 am
try DML or DDL Triggers..
Also you can use profiler to keep track of who doing what or server side traces....
Maninder
www.dbanation.com
June 25, 2009 at 10:24 am
a DML trigger tells you who CHANGED data...doesn't help with trying to find who SELECTED data.
a DDL trigger tells you who CHANGED a table structure, , so it again does not help with finding out who SELECTED data.
you'd have to add a trace to start tracking that; there is nothing "built-in" that keeps track of SELECT statements automatically.
you can add a server side trace so you could track it in the future, but there is no way to track anything that already happened in the past. Like Las Vegas, what happens in the past, stays in the past, until you add a trace.
here's an example, which creates a trace and a view to see the trace.
once in place, you could select where the table in question exists in the textdata, and see the login,hostname, and other ifno to see who peeked at a given table.
Create procedure sp_AddMyTrace
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
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
--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='sp_DMLTrace')
BEGIN
SET @sql = 'ALTER VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END
Lowell
June 25, 2009 at 10:43 am
Thanks. I don't really understand all that is in that trace code, but I will try to digest it. I am new at this DBA stuff and don't know all the best practices and legalities involving payroll databases.
thanks for this. Let's see what I can do with it.
June 25, 2009 at 11:13 am
easy enough middletree;
add the procedure and run it on your test server.
then immediately run this statement:
select * from sp_DMLTrace
you'll get results for at least 5 rows, which include the commands you just executed: select * from sp_DMLTrace
if you scroll through the columns, you can see the login info, the databaseid, hostname of the machine that was used, and a lot more..
this will capture all the SQL commands or calls to execute procedures against your whole server until you turn it stop or delete it.
a server side trace is very lightweight, and won't affect performance.
now you want to prove the concept works for you:
switch to your database, do a SELECT TOP 1 FROM YOURTABLENAME,
then do a select * from sp_DMLTrace where TEXTDATA LIKe '%YOURTABLENAME%'
you'll see you are able to trace who queried against your specific table.
Hope that helps
middletree (6/25/2009)
Thanks. I don't really understand all that is in that trace code, but I will try to digest it. I am new at this DBA stuff and don't know all the best practices and legalities involving payroll databases.thanks for this. Let's see what I can do with it.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply