February 19, 2014 at 3:28 am
Hi,
i have do replace a sql-server 2005 with 2008/R2. We do not know which client have access to which database, as this has not been documented. :w00t: Is there a way, for a period of time to monitor all connection activities and to monitor?
-which User or IP have access to
-what database
-maybe other information like time, table, view, SP ...
Thank you
Regards
Nicole
February 19, 2014 at 3:43 am
You can use SQL profiler for that
February 19, 2014 at 4:01 am
leon.booij (2/19/2014)
You can use SQL profiler for that
Yes, you could, but if we're talking about a Production system then I would not advise running a Profiler trace because it's going to need to run for potentially a long time and the overhead associated with a Profiler trace is not insignificant.
If you want to go down the tracing route then a server-side trace is a better option.
The system views sys.dm_exec_connections and sys.dm_exec_sessions could be queried periodically via a SQL Agent job and the results written to a table/tables for later inspection. Bear in mind that many system views are cleared when an instance is restarted. Often applications connect to a database through a single login so it can be difficult to identify individual users running the application
Looking at the server logins and database users may give you a clue and it doesn't hurt to ask around the user community.
Regards
Lempster
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply