find who is accessing a database

Is there an easy way to find out who is currently accessing a particular
database? We use SQL authentication.
Thanks.How about sp_who or sp_who2?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Kelley" <tkelley@.company.com> wrote in message news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
> Is there an easy way to find out who is currently accessing a particular
> database? We use SQL authentication.
> Thanks.
>|||Do these stored procedures return only active users?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
> How about sp_who or sp_who2?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> Is there an easy way to find out who is currently accessing a particular
>> database? We use SQL authentication.
>> Thanks.|||What do you mean by "active"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Kelley" <tkelley@.company.com> wrote in message news:%235u8reJwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> Do these stored procedures return only active users?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
>> How about sp_who or sp_who2?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> Is there an easy way to find out who is currently accessing a particular
>> database? We use SQL authentication.
>> Thanks.
>|||Jus to add, you can also use sp_lock or the following query:
select p.*
from syslockinfo l, sysprocesses p
where l.req_spid = p.spid
and l.rsc_type = 2
and l.rsc_dbid = db_id('yourDB')
This query or sp_lock will capture anyone that accesses a particular
database, including the one whose current database is not the one you are
investigating. For instance, you can run a query accessing pubs from master,
sp_who will show master, but not pubs. sp_lock or the above query will show
that you are putting a DB lock on pubs, thus you are accessing pubs.
Linchi
"Tim Kelley" wrote:
> Do these stored procedures return only active users?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
> > How about sp_who or sp_who2?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Tim Kelley" <tkelley@.company.com> wrote in message
> > news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >> Is there an easy way to find out who is currently accessing a particular
> >> database? We use SQL authentication.
> >>
> >> Thanks.
>
>|||Tim Kelley wrote:
> Is there an easy way to find out who is currently accessing a particular
> database? We use SQL authentication.
> Thanks.
>
Define "currently". If you want to know, RIGHT NOW, at this VERY
MOMENT, who is using a database, then you can use sp_who, sp_who2, or
just query the master..sysprocesses table. Any of these will show you
the current connections to the database.
If you want to know, over a specific period of time, who used a
database, then you'll have to use Profiler to capture the activity in
the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql