Prev: Default datetime format
Next: Release free space reserved for tables
From: nzrdb6 on 13 May 2010 11:13
I'm trying to satisfy the boss who wants the ability to find out "who
did what when" on our DBs. Essentially this request is calling for
24x7 monitoring. Any advice out there please? Should I profile (and if
so what events etc) or poll sysprocesses in an indefinite loop? Are
there any other options?

Thanks from an accidental DBA.
From: Erland Sommarskog on 13 May 2010 17:43
nzrdb6 (alex.campbell(a)sqltechconsulting.co.uk) writes:
> I'm trying to satisfy the boss who wants the ability to find out "who
> did what when" on our DBs. Essentially this request is calling for
> 24x7 monitoring. Any advice out there please? Should I profile (and if
> so what events etc) or poll sysprocesses in an indefinite loop? Are
> there any other options?

You need to ask the boss what he wants. :-)

There used to be third-party tools out there, but they seem to fall off
the market. Maybe because this is not exactly trivial. One thing is
gathering the data. Another is to interpret it.

The simplest approach is to trace the SP:RPCCompleted and SQL:BatchCompleted
events, but you will get a *lot* of information.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Uri Dimant on 14 May 2010 03:39
Hi
Long time ago I created the below trigger on the client's site. They want to
track down who alter/create/drop columns indexes in the database
Take a look at client_net_address column that represent IP address of the
client machine

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

<> 'CREATE_STATISTICS' AND @data.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(100)')

<> 'UPDATE_STATISTICS'

BEGIN

INSERT INTO DDLChangeLog

(

EventType,

ObjectName,

ObjectType,

tsql ,

Session_IPAddress

)

SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]',

'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]',

'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',

'nvarchar(max)'), client_net_address

FROM sys.dm_exec_connections WHERE session_id=@@SPID

END

;



ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE

"nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message
news:20ec3f3c-a231-4560-bdc5-a68f922b78f1(a)n15g2000yqf.googlegroups.com...
> I'm trying to satisfy the boss who wants the ability to find out "who
> did what when" on our DBs. Essentially this request is calling for
> 24x7 monitoring. Any advice out there please? Should I profile (and if
> so what events etc) or poll sysprocesses in an indefinite loop? Are
> there any other options?
>
> Thanks from an accidental DBA.


From: Geoff Schaller on 14 May 2010 22:29
Hello (please insert name here).

What you ask requires nothing less than a complete copy of every edit to
every row and this results in a LOT of data.

You could do this with triggers by having a "mirror" database where
every changed row is copied into the mirror for every table, with an
additional column of the user name and date time of the change.

Another way is to have an audit table that a trigger or something again
copies the table name, column name, new value, user name and date time
for every change. This requires a lot of coding logic to isolate the
changes for storage.

Then you have to decide if a "bulk" operation should then record every
row or value it touches. For example a payroll process that updates all
employees superannuation rate on a date. Or a balance process that
touches 100,000 rows...

This is something that has to be designed an planned in detail because
the resource costs are high.

Cheers,

Geoff Schaller
Software Objectives


"nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message
news:20ec3f3c-a231-4560-bdc5-a68f922b78f1(a)n15g2000yqf.googlegroups.com:

> I'm trying to satisfy the boss who wants the ability to find out "who
> did what when" on our DBs. Essentially this request is calling for
> 24x7 monitoring. Any advice out there please? Should I profile (and if
> so what events etc) or poll sysprocesses in an indefinite loop? Are
> there any other options?
>
> Thanks from an accidental DBA.

 | 
Pages: 1
Prev: Default datetime format
Next: Release free space reserved for tables