Thursday, December 27, 2012

SQL Server: Client IP Along with DDL Change Log Using Service Broker

Last day, we have discussed all three possible methods for DDL Change Log and as per my suggestions, if you don’t need to conditionally allow/disallow changes then Service Broker is the best way to capture these changes. This method additionally allows you to submit change information to a separate instance on internet as a loosely coupled message.

One of blog reader raised a question that what else we need to add in script if we also need to capture machine IP from where change is coming.

Well answer is simple. We already have information of SPID so we can use this SPID and get client machine IP address by querying sys.dm_exec_connections.  
Change already defined stored procedure as following.

1 comment:

  1. Hi Aasim,
    Unfortunately doing it will produce unreliable information of the remote host IP since the SB notification is executed async and a good chance that the session that originally executed the DDL statement had already closed the connection. Moreover - if you send the notification to a different SQL instance than the SPID you are querying is completely out of context.
    BoazG. SQL DBA.


All suggestions are welcome