Alan Moseley IT Consultancy

Blog Entry

10/17/2008 - SQL query to see which users are accessing databases

How do you allow a user to see which other users are accessing SQL databases without installing management consoles etc? Easy, write a report (using Crystal or Excel maybe) which connects to the server's 'master' database and use the following select statement:-

SELECT sysdatabases.name, sysprocesses.nt_username FROM syslocks INNER JOIN sysprocesses ON syslocks.spid = sysprocesses.spid INNER JOIN
sysdatabases ON syslocks.dbid = sysdatabases.dbid
GROUP BY sysdatabases.name, sysprocesses.nt_username
ORDER BY sysdatabases.name, sysprocesses.nt_username