SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Link Session info to CDC or in other words Auditing with CDC


Link Session info to CDC or in other words Auditing with CDC

Author
Message
gvarol
gvarol
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 356
Comments posted to this topic are about the item Link Session info to CDC or in other words Auditing with CDC
Robert Davis
Robert Davis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10706 Visits: 1661
Very nice article! I like this idea. You can also add the SPID of the session that spawned the trigger using @@SPID, and once you have that value, you also open doing things like grabbing data like the SQL text or query plan or session settings via the various DMVs.


My blog: SQL Soldier
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
gvarol
gvarol
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 356
For months I had done the whole data generation from within costly triggers, when the cost became very expensive I went back to using CDC but having done all kind of tricks in triggers initially opened my mind to combining triggers with CDC :-)

Initially I started getting the DBCC Inputbuffer to get the sql statement that initiated the DML (during the trigger execution that is the only way to find the root DML), later on I realized I did not have much need for it and wanted to get out of the trigger as fast and removed it. To store the spid and analyze it later makes more sense, with no overhead during the trigger execution.

I wanted to point out the select statement with Before and After values at the end of the script, it's does the trick of displaying what's changed only in a more Audit like session, using the values clause to unpivot the data costing very few too.
Oscar Zamora
Oscar Zamora
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 177
Great stuff.

Wondering if there is a way to extend this to include application information, like the application username and ip address that triggered the CRUD operation. We are using NHibernate to generate the SQL and we need to be able to audit changes and know who did it and from where.

Any feedback would be appreciated.

Thanks



gvarol
gvarol
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 356
You can always retrieve more information from system views or dmv's as below. Whatever information you will want to pull out by testing local session and whatever is available, then you will change the table structure that will hold the data and the insert statement in the trigger if that's what you are looking for.


-- older version but still supported
SELECT [SPID], [kpid], [blocked], [waittype], [waittime], [lastwaittype], [waitresource], [dbid], [uid], [cpu], [physical_io], [memusage], [login_time], [last_batch], [ecid], [open_tran], [status], [sid], [hostname], [program_name], [hostprocess], [cmd], [nt_domain], [nt_username], [net_address], [net_library], [loginame], [CONTEXT_INFO], [sql_handle], [stmt_start], [stmt_end], [request_id]
FROM master.sys.sysprocesses (NOLOCK) WHERE SPID = @@SPID

-- newer versions
SELECT [session_id], [most_recent_session_id], [connect_time], [net_transport], [protocol_type], [protocol_version], [endpoint_id], [encrypt_option], [auth_scheme], [node_affinity], [num_reads], [num_writes], [last_read], [last_write], [net_packet_size], [client_net_address], [client_tcp_port], [local_net_address], [local_tcp_port], [connection_id], [parent_connection_id], [most_recent_sql_handle] FROM sys.dm_exec_connections (NOLOCK) WHERE session_id = @@SPID

SELECT [session_id], [login_time], [HOST_NAME], [program_name], [host_process_id], [client_version], [client_interface_name], [security_id], [login_name], [nt_domain], [nt_user_name], [status], [CONTEXT_INFO], [cpu_time], [memory_usage], [total_scheduled_time], [total_elapsed_time], [endpoint_id], [last_request_start_time], [last_request_end_time], [READS], [writes], [logical_reads], [is_user_process], [text_size], [LANGUAGE], [date_format], [date_first], [QUOTED_IDENTIFIER], [ARITHABORT], [ANSI_NULL_DFLT_ON], [ANSI_DEFAULTS], [ANSI_WARNINGS], [ANSI_PADDING], [ANSI_NULLS], [CONCAT_NULL_YIELDS_NULL], [transaction_isolation_level], [LOCK_TIMEOUT], [DEADLOCK_PRIORITY], [row_count], [prev_error], [original_security_id], [original_login_name], [last_successful_logon], [last_unsuccessful_logon], [unsuccessful_logons], [group_id] FROM sys.dm_exec_sessions (NOLOCK) WHERE session_id = @@SPID

SELECT [session_id], [request_id], [start_time], [status], [command], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [USER_ID], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [CONTEXT_INFO], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [READS], [writes], [logical_reads], [text_size], [LANGUAGE], [date_format], [date_first], [QUOTED_IDENTIFIER], [ARITHABORT], [ANSI_NULL_DFLT_ON], [ANSI_DEFAULTS], [ANSI_WARNINGS], [ANSI_PADDING], [ANSI_NULLS], [CONCAT_NULL_YIELDS_NULL], [transaction_isolation_level], [LOCK_TIMEOUT], [DEADLOCK_PRIORITY], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code], [group_id], [query_hash], [query_plan_hash] FROM sys.dm_exec_requests (NOLOCK)
WHERE session_id = @@SPID
gvarol
gvarol
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 356
If you want to find out the actual statement executed at the start of the sql call stack than you can get it (up to 4000 characters) by calling DBCC INPUTBUFFER. The dmvs to get the same data does not return the start of the call stack.

DECLARE @SQL NVARCHAR(4000)
DECLARE @IBuf TABLE (EventType nvarchar(30) NULL, Parameters INT NULL, EventInfo nvarchar(4000) NULL)

INSERT @IBuf
EXEC('DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS')
SELECT @SQL = EventInfo FROM @IBuf

SELECT @SQL
Disney Hammerschmidt
Disney Hammerschmidt
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 185
Great Article,

I will make some changes in table, and post here later.

TY for share. :-P
Iwas Bornready
Iwas Bornready
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29632 Visits: 885
Thanks for another good script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search