October 19, 2018 at 7:14 am
We are looking to track usage of objects that use linked server connections to identify calling client_hostname and username.
Somewhat successful in doing this with Extended Events using rpc_completed and sql_batch_completed filtering on sqlserver.sql_text like_i_sql_unicode_string %<object name>%.
Works well enough to capture direct calls. However, some of these objects are buried multiple layers deep. (coding standards aside...) I.e. a linked server is used in a view that is reference by another view that is reference by a stored procedure. (actually some cases are buried much deeper and/or behind Synonyms; hence comment about coding standards...)
I fully suspect the answer to be that we need to document/monitor for all objects that are in the dependency chain for each object.
Would just like to ask if there might be an "easy" way to do this that I've overlooked? I.e. use another event/filter
Thanks!
October 29, 2018 at 8:57 am
Can you set a trap on the other side of the linked server? It is not nearly as effective, but it will give you every time the linked server is used.
October 29, 2018 at 9:46 am
You could set up an Extended Events session and capture oledb_provider_initialized. Presumably there are similar events for other providers, in case any of your linked servers don't use OLDEB.
John
November 23, 2018 at 8:35 am
I have used Redgate's SQL Search SSMS plugin for this. I search every object and all object types for the name of the linked server and see what refers to it as typically the linked server is referred to in a four part naming convention i.e. Linkedservername.databasename,schemaname.tablename.
If you then find any synonyms that refer to it you can search for them using SQL search to see what refers to them.
November 23, 2018 at 8:38 am
Rob Buecker - Friday, October 19, 2018 7:14 AMWe are looking to track usage of objects that use linked server connections to identify calling client_hostname and username.
Somewhat successful in doing this with Extended Events using rpc_completed and sql_batch_completed filtering on sqlserver.sql_text like_i_sql_unicode_string %<object name>%.
Works well enough to capture direct calls. However, some of these objects are buried multiple layers deep. (coding standards aside...) I.e. a linked server is used in a view that is reference by another view that is reference by a stored procedure. (actually some cases are buried much deeper and/or behind Synonyms; hence comment about coding standards...)
I fully suspect the answer to be that we need to document/monitor for all objects that are in the dependency chain for each object.
Would just like to ask if there might be an "easy" way to do this that I've overlooked? I.e. use another event/filter
Thanks!
"Unplug" it:exclamationmark:
😎
Sooner than later, you will know all the usage😉
November 23, 2018 at 9:50 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply