Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

You might use the system view sys.sysprocesses:

select sp.hostname, sp.program_name, sp.*
from sys.sysprocesses sp
where spid > 50 -- user processes

Or you can use the Activity Monitor (from Management Studio) -> Processes -> column Application Name.

share|improve this answer

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)

share|improve this answer
    
For Access the ProgramName will be listed as the database file, NOT MS Access or something. –  JNK Nov 22 '11 at 15:48
2  
This will vary, which is why I put the caveat. Using Access 2010 via ODBC yielded "Microsoft Office 2010" in my test. –  Tevo D Nov 22 '11 at 15:59

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 exec sp_who2.

If you want to track something other than active connections, you'd have to create a trace using SQL Profiler.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.