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.
Hi Aasim,
ReplyDeleteUnfortunately 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.