September 22, 2011 at 4:30 pm
Hello all, i am new to sql server dba and have been a oracle guy. Is there a view that i can query to get how many active users i have connected to my database right now (i know i can start active monitor tool) but looking for a way to query it
And also is there a view or anything where i can get number of concurrent user or lets says max number of user ever connected to my database in the past ??
September 22, 2011 at 4:57 pm
abdul.irfan2 (9/22/2011)
Hello all, i am new to sql server dba and have been a oracle guy. Is there a view that i can query to get how many active users i have connected to my database right now (i know i can start active monitor tool) but looking for a way to query itAnd also is there a view or anything where i can get number of concurrent user or lets says max number of user ever connected to my database in the past ??
There is a stored procedure called sp_who or sp_who2. This will show you all the active spids on your sql server. One of the columns that is returned is the username. Several people have written some custom sp_who type procs that do a lot of different things, such as showing what sql is currently being executed.
Hope that helps.
September 22, 2011 at 6:21 pm
Thanks for the info,
what about any historical data for number for user that logged on ?
September 22, 2011 at 7:15 pm
abdul.irfan2 (9/22/2011)
Thanks for the info,what about any historical data for number for user that logged on ?
I am not familiar with anything in sql server. Most of the stuff I have seen use sp_who2 to collect data and then put it into a historical data table over time. Still, there might be some place that it is kept that I don't know about.
September 23, 2011 at 5:00 am
Hi Abdul,
I do use the below which I have done when asked to check for active user on a specific Db.
you might have to tweak a bit.
USE [DataBaseName]
select suser_sname(SID) as ServerLevelLogin,
name as DatabaseLevelUser,
principal_id,
type_desc,
create_date,
modify_date
from sys.database_principals
where principal_id >= 5 and is_fixed_role <> 1
Thanks
Imran
September 23, 2011 at 5:16 am
Hello,
Please look up sys.dm_exec_sessions.
Run the query below to get all active users spid, their login names and other information.
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1
AND session_id <>@@SPID
Hope that helps.
Thanks,
Vasu
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy