How Can I Determine Who or What is Accessing a Particular Database

Question

How Can I Determine Who or What is Accessing a Particular Database

JC-3113

SSCrazy Eights

Points: 8371
More actions
August 31, 2010 at 2:26 pm

#148561

Hi Folks
I am sure this has been asked a thousand times, but what sql can i run or system tables can I access to determine who or what is accessing a particular database.
Thanks
jim

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

sp_who (less detail) or sp_who2 (more detail) are the most popular. They are pretty much what you see in the Activity Monitor, which is the GUI version.

With DMVs a good one is: sys.dm_exec_sessions (check example A on the link)

http://msdn.microsoft.com/en-us/library/ms176013.aspx

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi Shawn

thanks very much

i will give them all ashot

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 3

Hi Shawn

i do not see the dbname in the table (sys.dm_exec_sessions) as i do in sp_who or sp_who2.

and i do not see where i can use sys.dm_exec_sessions to get the dbname

any ideas

Thanks

Jim

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

Well that is true, sys.dm_exec_sessions is for seeing what a particular user/login is doing. Forgot about that.

Try using sys.sysprocesses (http://msdn.microsoft.com/en-us/library/ms179881%28SQL.90%29.aspx). This one has the dbid column to filter by the databse ID.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 5

Thanks Shawn

i was able to use two tables to get what i wanted for now:

select

a.dbid,

CAST (b.name as varchar(90)) as database_name,

CAST (a.loginame as varchar(36)) as loginame,

CAST (a.hostname as varchar(18)) as hostname,

cpu,

a.login_time,

CAST (a.status as varchar(18)) as status,

CAST (a.program_name as varchar(50)) as program_name,

from

sys.sysprocesses a,

sys.sysdatabases b

where

a.dbid=b.dbid

order by

b.name;

go

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

sorry

had an extra comma after program_name:

select

a.dbid,

CAST (b.name as varchar(90)) as database_name,

CAST (a.loginame as varchar(36)) as loginame,

CAST (a.hostname as varchar(18)) as hostname,

cpu,

a.login_time,

CAST (a.status as varchar(18)) as status,

CAST (a.program_name as varchar(50)) as program_name

from

sys.sysprocesses a,

sys.sysdatabases b

where

a.dbid=b.dbid

order by

b.name;

go

pankushmehta Ten Centuries Points: 1217 More actions · Answer 7

JC-3113 (8/31/2010)
sorry
had an extra comma after program_name:
select
a.dbid,
CAST (b.name as varchar(90)) as database_name,
CAST (a.loginame as varchar(36)) as loginame,
CAST (a.hostname as varchar(18)) as hostname,
cpu,
a.login_time,
CAST (a.status as varchar(18)) as status,
CAST (a.program_name as varchar(50)) as program_name
from
sys.sysprocesses a,
sys.sysdatabases b
where
a.dbid=b.dbid
order by
b.name;
go

You can remove the join to sysdatabases by using the db_name (dbid).

WayneS SSC Guru Points: 95409 More actions · Answer 8

pankushmehta (8/31/2010)
JC-3113 (8/31/2010)
sorry
had an extra comma after program_name:
select
a.dbid,
CAST (b.name as varchar(90)) as database_name,
CAST (a.loginame as varchar(36)) as loginame,
CAST (a.hostname as varchar(18)) as hostname,
cpu,
a.login_time,
CAST (a.status as varchar(18)) as status,
CAST (a.program_name as varchar(50)) as program_name
from
sys.sysprocesses a,
sys.sysdatabases b
where
a.dbid=b.dbid
order by
b.name;
go
You can remove the join to sysdatabases by using the db_name (dbid).

Which will have to query the sysdatabases behind the scenes. You might as well as keep it.

HOWEVER: sys.sysprocesses and sys.sysdatabases are both backwards-compatible views. Unless they have some information that you can't get anywhere else, you should be using the new sys.databases, etc. views.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

There are also a couple more options.

there is a script called sp_whoisactive by Adam machanic here

I also wrote an article with a script that may be of use here[/url]

I also have other scripts on my blog (in my sig).

To get historical information, there are triggers and auditing that would be useful.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

JC-3113

SSCrazy Eights

Points: 8371

September 1, 2010 at 10:12 am

#1215135

Thanks pankushmehta

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

Hi Jason

unfortunately i work at an AFB and they block alot of sites & blogs so i cannot get to them

i will have to look at them from home

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 12

Thanks Waynes

i am new to sql server sql; coming from oracle

will have to look at these tables you mention

Jim

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

JC-3113 (9/1/2010)
Hi Jason
unfortunately i work at an AFB and they block alot of sites & blogs so i cannot get to them
i will have to look at them from home
Jim

It would be worth your time. That's unfortunate that many blogs are blocked - I have the same issue here.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Arjun SreeVastsva SSCertifiable Points: 7137 More actions · Answer 14

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,

loginame as LoginName FROM sys.sysprocesses

WHERE dbid > 0 GROUP BY dbid, loginame