How to audit a single login

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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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".

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply