How to audit a single login

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » How to audit a single login

How to audit a single login

Rate Topic

Display Mode

Topic Options

Author

Message

Chris Burk

Posted Tuesday, March 05, 2013 11:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 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

Lowell

Posted Tuesday, March 05, 2013 12:08 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

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

Chris Burk

Posted Tuesday, March 05, 2013 12:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 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

Lowell

Posted Tuesday, March 05, 2013 12:50 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

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 »

Permissions