Finding active Users in SQL

      I have users that sometimes don't log off their PC's and keep a connection to the P21 Program we use. This at times causes us to run out of licenses for users that need to log in. Is there a SQL query I can run against the database to let me know who is actually connected to that particular database? Thanks
Who is Participating?
JestersGrindConnect With a Mentor Commented:
Sorry, I sent you my query for listing users and the number of connection to the server.  Try this one.

select spid, loginame, hostname, db_name(dbid) AS DBName
from master.dbo.sysprocesses
where db_name(dbid) = 'YourDatabase'


Lee SavidgeCommented:
If they're using named SQL logins then try exec sp_who or exec sp_who2
McottuliAuthor Commented:
Is there a way to limit that to a specific database? It seems to bring up all databases within SQL
You could use the dynamic management view sys.dm_exec_sessions.

SELECT login_name, count(session_id) as session_count
FROM  sys.dm_exec_sessions
GROUP BY login_name


McottuliAuthor Commented:
Greg Thanks, That one is much cleaner, but again is there a way to bring back results for a specific database, it seems to bring up connections from all databases.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.