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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gokhan Varol » Link Session info to CDC or in other words...

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

Rate Topic

Display Mode

Topic Options

Author

Message

gvarol

Posted Saturday, June 18, 2011 1:07 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 8:59 PM
Points: 20, Visits: 271

Comments posted to this topic are about the item Link Session info to CDC or in other words Auditing with CDC

Post #1127789

Robert Davis

Posted Sunday, June 19, 2011 10:37 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 1,559, Visits: 1,404

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
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #1127971

gvarol

Posted Monday, June 20, 2011 8:09 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 8:59 PM
Points: 20, Visits: 271

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 Smile

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.

Post #1128269

Oscar Zamora

Posted Thursday, June 23, 2011 11:11 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 30, 2012 3:32 PM
Points: 67, Visits: 168

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

Post #1130676

gvarol

Posted Thursday, June 23, 2011 11:48 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 8:59 PM
Points: 20, Visits: 271

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

Post #1130708

gvarol

Posted Thursday, June 23, 2011 11:55 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 8:59 PM
Points: 20, Visits: 271

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

Post #1130713

Disney Hammerschmidt

Posted Wednesday, February 08, 2012 5:38 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 24, 2012 8:56 PM
Points: 10, Visits: 116

Great Article,

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

TY for share. Tongue

Post #1248896

« Prev Topic | Next Topic »

Permissions