Is there a way I can check SQL Server to see who is accessing it using Microsoft Access (as opposed to a website)? I'm using SQL Server 2008R2 with the default logging settings.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You might use the system view sys.sysprocesses:
Or you can use the Activity Monitor (from Management Studio) -> Processes -> column Application Name.
The sp_who2 stored procedure lists all active connections and includes the ProgramName.
You could also select from sysprocesses (joining in whatever other information you require such as sysusers) but it's just easier to use sp_who2.
(caveat, this will only work for applications which set the name, some applications may not)
The active connection will show a Program Name of something like "2007 Microsoft Office system", depending on the version of Access being used. It's not foolproof, as the name can be set to arbitrary values with custom applications.
View the active connections with
If you want to track something other than active connections, you'd have to create a trace using SQL Profiler.