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

How to audit a single login Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 7:33 AM
Points: 2, Visits: 8
I have a request to elevate security on a single login. I would like to be able to audit all activity for this login while the security is elevated to make sure no data edits are made.

Is there a way to trace or audit all activity for a single login?
Post #1426970
Posted Tuesday, March 5, 2013 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 12,918, Visits: 32,088
there's a couple of ways to tackle this, but it depends on what the focus is:
if you want to absolutely prevent the login from UPDATE, you'd create a group, deny update, and then assign that person to the group.
CREATE ROLE NoUpdateForYou
EXEC sp_addrolemember 'db_denydatawriter','NoUpdateForYou'
EXEC sp_addrolemember 'NoUpdateForYou','Lowell'

if you want to just track whether they are doing updates at all, in an environment where they have update rights but shouldn't be doing so, one way would be to create a DML trace, and filter by username.


after the trace exists, you'd have to search the TEXTDATA column of the trace for INSERT UPDATE or DELETE in the text itself.

here's a DML trace looking for the specific login, "LOWELL", which is what you would change to your target login
--#################################################################################################
-- Scripting trace_id 3 from server DEV223
-- Trace Last Started/Restarted on Tuesday 2013/03/05 - 14:34:33:007
-- Scripted for Analysis on Tuesday 2013/03/05 - 14:34:39:340
--#################################################################################################
--declare variables for parameterizing the command
declare @traceidout int
declare @myoptions int
declare @mypath nvarchar(256)
declare @mymaxfilesize bigint
declare @mymaxRolloverFiles int
declare @mystoptime datetime
declare @on bit

set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.
set @mymaxfilesize = 50 --size in MB
set @mymaxRolloverFiles = 0 --number of files; ie if 5 files, start rewriting on rollover
set @mystoptime = NULL -- null if never ends, else a specific date
set @myoptions = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE
--This is the Actual Path on the scripted server.
SELECT @mypath = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\MyDMLtrace' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting

--for Portability reasons, we change this in the script right after this actual path to get the path for the default trace, which we assume to exist on the server.

SELECT @mypath = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
SELECT @mypath = @mypath + N'MyDMLtrace' --system appends .trc automatically for the filename

--#################################################################################################
--create the trace
exec sp_trace_create @traceid = @traceidout output,
@options = @myoptions,
@tracefile = @mypath,
@maxfilesize = @mymaxfilesize,
@stoptime = @mystoptime

--#################################################################################################
--Begin Event definitions
--#################################################################################################
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,63,@on --RPC:Completed,SqlHandle
exec sp_trace_setevent @traceidout,10,64,@on --RPC:Completed,SessionLoginName
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,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,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,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,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,26,@on --RPC:Completed,ServerName
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,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,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,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,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,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
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,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,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,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,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,25,@on --SQL:BatchCompleted,IntegerData
exec sp_trace_setevent @traceidout,12,26,@on --SQL:BatchCompleted,ServerName
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,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,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,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

--#################################################################################################
--End Event definitions
--#################################################################################################

--#################################################################################################
--begin filter definitions
--#################################################################################################

-- WHERE 1 = 1
-- AND LoginName LIKE N'LOWELL'
-- AND LoginName <> NULL
exec sp_trace_setfilter @traceidout,11,0,6, N'LOWELL'
exec sp_trace_setfilter @traceidout,11,0,1, NULL
--#################################################################################################
---end filter definitions
--#################################################################################################

---final step

--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



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 #1426977
Posted Tuesday, March 5, 2013 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 7:33 AM
Points: 2, Visits: 8
I get the following error when attempting to execute:

exec sp_trace_create @traceid = @traceidout output,
@options = @myoptions,
@tracefile = @mypath,
@maxfilesize = @mymaxfilesize,
@stoptime = @mystoptime,
@filecount = @mymaxRolloverFiles


Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@traceidout".
Post #1426993
Posted Tuesday, March 5, 2013 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 12,918, Visits: 32,088
grab it again; i edited it and retested it on my machine, it seems to work now.

this is what i would use to review the trace:

--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 4 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value



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 #1427002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse