At times as a DBA this question will be asked. “How many users are connected to my database right now?”. There are various approaches for getting this information. Here are the two scripts that I usually use.
SQL Server 2000:
select db_name ( dbid ) as [Database Name] , count (*) as [Sessions] from sysprocesses where db_name(dbid)= 'YourDatabaseName' group by db_name ( dbid ) order by db_name ( dbid )
SQL Server 2005:
select db_name ( resource_database_id ) as [Database Name] , count (*) as [Sessions] FROM sys . dm_tran_locks where resource_type = 'DATABASE' and db_name ( resource_database_id ) = 'YourDatabaseName' group by db_name ( resource_database_id ) order by db_name ( resource_database_id )