Who is online?  164 guests and 0 members
Home  »  Blogs  »  indupriya  »  Determine users connected to sqlserver database

Determine users connected to sqlserver database

change text size: A A A

posted  3/21/2010 8:02:22 PM  by indupriya

There are three ways to find who is connected to a database in sqlserver. 

First one:

  • Use the SQL SERVER Management Studio -- Management -- Activity Monitor
  • This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

 

Second One:

  • Use the built in stored procedure called sp_who2
  • Run the command exec sp_who2
  • This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.

Third One:

  • Use the script

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'

 

This gives the status, login name and host name for the database you specify.

 

 

 

tags :    sqlserver
  Rate This Blog Entry:  register  or  login

Author

indupriya indupriya (Member since: 2/22/2010 1:45:30 PM)

View indupriya 's profile

Comments (2)

nitya
3/26/2010 9:18:24 PM nitya said:
I suggest you should use the much advances sp_whoisactive by MVP Adam Machanic (http://sqlblog.com/files/folders/beta/entry23455.aspx) to find resource intensive sessions
by
indupriya
4/15/2010 12:15:39 AM indupriya said:
Hi Nitin Can you please give an updated link? This link is not working Thanks indupriya
by

Leave a comment


 
Type the characters:
 *
 
   

Join Business Intelligence Developer Network for FREE Today!

It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.

enter your email address:

Blogs RSS Feed

indupriya's latest blog posts

  • What are plan guides?
    9/2/2010 1:34:14 AM by indupriya  - Views: 6265  - Comments: 0
    Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here ...
  • Code Camp in Auckland New Zealand -- 29th August 2010
    8/25/2010 9:11:47 PM by indupriya  - Views: 100  - Comments: 0
    The MS Communities Code Camp & Technology Summit is a Free , non-commercial event, open to everybody with an interest in any of the technologies being discussed. Development This year we will be r...
  • BIDS Helper
    8/24/2010 1:18:32 AM by indupriya  - Views: 95  - Comments: 0
    Today I came across the BIDS Helper while searching for something. This is a free add-in for Visual Studio with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server...
  • 24 hours PASS in September 2010
    8/11/2010 7:55:06 PM by indupriya  - Views: 118  - Comments: 0
    The 2nd instalment of 24 hours of PASS is in September and below are the details. Join an exceptional lineup of SQL Server and BI experts from around the world as they present a sneak peek of PASS Sum...
  • Convert a date to show AM/PM
    7/29/2010 10:48:57 AM by indupriya  - Views: 161  - Comments: 0
    Today I was working on a date column -- create_date. I wanted to show whether the row was created in the morning or in the evening i.e., AM/PM This is what I did to achieve this. SELECT rn_create_date...
  • Insert values into an Identity column
    7/29/2010 4:15:16 AM by indupriya  - Views: 176  - Comments: 0
    In sql server most of the identity columns are oftern used to generate a unique number for each row in a specific table. If we try to insert a value in the normal way, it does not allow us to enter va...
  • Distinct Count in Excel
    7/29/2010 3:40:53 AM by indupriya  - Views: 228  - Comments: 0
    Today I had to calculate the unique number of records in an excel spreadhseet. First I thought I could I achieve this by pivot table. But I could not do a distinct count. Here is the solution I found ...
  • Automatically open a New Query window in SSMS
    7/29/2010 1:51:43 AM by indupriya  - Views: 198  - Comments: 0
    If we are used to the Query Analyser of SQL Server 2000, we might find it frustrating that the query window does not automatically come up when you start SSMS. You have to either press Ctrl+N or Click...
  • Data warehouse vs. OLAP
    7/19/2010 8:17:41 PM by indupriya  - Views: 272  - Comments: 3
    Data warehouse ad OLAP terms are commonly used in the Business Intelligence world. Below are some points that differentiate them as per my understanding. Data Warehouse (DW) – Data warehouse is a term...
  • Slowly Changing Dimension – Basics
    7/19/2010 2:02:29 AM by indupriya  - Views: 266  - Comments: 0
    Slowly Changing Dimension is a component in SSIS. The term of SCD (slowly changing dimension) is used to describe changing row attribute in a dimension or lookup table, such as change of employee’s ma...
  • silverlight PivotViewer has been released
    7/15/2010 12:46:26 AM by indupriya  - Views: 242  - Comments: 0
    The Silverlight PivotViewer has been released. The Silverlight PivotViewer makes it easier to interact with massive amounts of data on the web in ways that are powerful, informative, and valuable. Piv...
  • Building a cube without a datasource -- SSAS
    6/24/2010 8:22:56 PM by indupriya  - Views: 361  - Comments: 0
    Sometimes we might have to build a cube without a relational data source. In this scenario, we can build a cube without a datasource focusing on designing the dimensions and measures and then BIDS can...
  • Perspective in SSAS
    6/21/2010 5:46:32 AM by indupriya  - Views: 361  - Comments: 0
    SSAS Perspectives Perspectives are a way to reduce the complexity of cubes by hidden elements like measure groups, measures, dimensions, hierarchies etc. Perspectives do not require any storage beyond...
  • SSIS Expression Tester
    6/18/2010 12:13:31 AM by indupriya  - Views: 164  - Comments: 0
    The expression tester tool is a standalone application that allows you to easily develop and test expressions. It is like a workbench to quickly try things without having to open a package or mess up ...
  • 24 hrs of SQL PASS recordings are available now
    6/17/2010 8:38:58 PM by indupriya  - Views: 173  - Comments: 0
    24 Hours of PASS Recordings Ready for Viewing! 24 Hours of PASS brought an exceptional lineup of SQL Server and BI experts from around the world in 24 back-to-back webcasts starting at 12:00 GMT (UTC)...
  • Microsoft LogParser
    6/15/2010 9:28:40 PM by indupriya  - Views: 154  - Comments: 0
    Today I was frustrated with the built in error log in SQL SERVER 2005 and did some research and found the following link to Microsoft LogParser that can be used to search the error logs. http://www.mi...
  • Aggregates vs. calculated measure
    6/13/2010 11:08:21 PM by indupriya  - Views: 255  - Comments: 0
    Aggregates In SSAS any column in a fact table that is not a foregin key is set up as a measure. The default aggregation is a SUM. If you need another form of aggregate like an average it may not be si...
  • BI xpress -- Installation
    6/9/2010 2:14:22 AM by indupriya  - Views: 167  - Comments: 3
    Today I have installed BI xPress on my machine successfully. I have opted for complete installation. I was excited to use it and when the BI xPress was launched, I was a bit confused on how to use it ...
  • Auditing sql server logins in sql server 2005
    6/7/2010 9:54:30 PM by indupriya  - Views: 429  - Comments: 0
    There are two views provided by sql server 2005 that can be used for auditing sql server logins. They are sys.server_pricipals and sys.sql_logins. select * from sys.server_pricipals The type field in ...
  • Finding the worst performing datafile.
    6/1/2010 9:54:51 PM by indupriya  - Views: 218  - Comments: 0
    We can use two functions provided by sql server to find the worst performing data file. They are sys.dm_io_virtual_file_stats and sys.master_files The main columns to be used from sys.dm_io_virtual_fi...
  • Deploying SSAS databases
    5/31/2010 12:35:39 AM by indupriya  - Views: 324  - Comments: 0
    Deploying SSAS databases I have found that there are three ways for deploying an Analysis Services database onto an Analysis Services server Using the Analysis Services Deployment Wizard Use the Analy...
  • Create a data dictionary in sql server 2005
    5/26/2010 9:41:10 PM by indupriya  - Views: 972  - Comments: 0
    Did you know that creating a data dictionary is easy if you use extended properties? Here is how I have done it with the database. The first step is to make sure the database has the extended properti...
  • Substring syntax in SQL
    5/25/2010 8:10:59 PM by indupriya  - Views: 196  - Comments: 0
    Yesterday I was trying to get a part of a string from a field where the first comma occurs. For example there is a field let us call it 'Descriptor' which is a combination of a few fields let us say "...
  • Copy data from one database into a similar table in another database
    5/24/2010 9:17:44 PM by indupriya  - Views: 843  - Comments: 0
    I had this requirement today where I had to copy from a table in one database into a similar table in another database. the code I have used is insert into database1.dbo.table1 select * from database2...
  • SSAS - Attribute Relationships
    5/21/2010 10:27:22 AM by indupriya  - Views: 545  - Comments: 0
    I have listened to Craig Utley's SelectViews show on SSWUG.org . He gave the analysis tip that defining attribute relationships in SSAS is important for aggregations. What are attribute Relationships?...
Blogs RSS Feed

Latest community blog posts

  • Pirates of PASS: Curse of the Elections Process
    13 hours, 30 minutes ago by sqlchicken  - Views: 7843  - Comments: 3
    Pirate Code: They're more like guidelines really... The last few weeks we've watched the drama unfold in regards to the PASS BOD elections. We've seen people attack criticize the NomCom, the board, PA...
  • What are plan guides?
    9/2/2010 1:34:14 AM by indupriya  - Views: 6265  - Comments: 0
    Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here ...
  • Did You Know..
    9/1/2010 8:22:35 PM by britmccullar  - Views: 6879  - Comments: 0
    Unfortunately, I don't have one big topic to write about in this post, so I decided to share some observations from the past few weeks - little things that might be of interest in the next report you ...
  • Creating a PerformancePoint 2010 Data Source
    9/1/2010 3:23:11 AM by DevinKnight  - Views: 2800  - Comments: 0
    In a previous post I wrote about getting started with PerformancePoint 2010. In that post you learned about what PerformancePoint is and where to open in in SharePoint 2010. Your next steps are to con...
  • Rock the Vote - Outlook Voting Buttons
    8/31/2010 5:46:06 PM by ChrisAlbrektson  - Views: 6023  - Comments: 1
    So today at the office I was faced with one of the more common and important questions that come up regularly and that was ‘Where are we going for lunch?’ This usually in tells a few minor arguments a...
  • Avoiding dynamic SQL - COALESCE( ) and your parameters
    8/31/2010 3:00:38 PM by KeithHyer  - Views: 7137  - Comments: 1
    Problem: Everyone that's programmed a system that users will interact with has run across a statement like this one in the fact-finding stage. "I want to be able to search on phone number. I can't ima...
  • "Drive" a great book
    8/31/2010 1:23:59 PM by MikeDavis  - Views: 2255  - Comments: 0
    If you only get a chance to read one book this year, it should be Drive , The Surprising Truth about what Motivates Us. This book has made me rethink how I am motivated and how I can motivate others. ...
  • An other Example of Calculation-Dimension
    8/31/2010 12:47:44 AM by Qazafi  - Views: 4104  - Comments: 0
    Recently one of my clients would like to see their Revenue figures represented some of the time as a positive (+VE) value and some time as a negative (-VE) value. They would like to have the ability t...
  • Template Parameters - Little Time Savers
    8/30/2010 8:45:36 PM by KeithHyer  - Views: 13373  - Comments: 3
    Lately, I've been enjoying various shows on the Science channel about Time and Space. No one yet understands exactly what time is yet - still, everyone wants to save some. I'd like to show how one mig...
  • Pragmatic Works Shines at 2010 Jacksonville Code Camp
    8/30/2010 3:24:25 AM by timmurphy  - Views: 2876  - Comments: 0
    I attended the 2010 Jacksonville Code Camp Sataurday and really was impressed with Pragmatic Works. The entire Business Intelligence stack was covered by speakers from PW including Brian Knight, Adam ...
  • Keyboard Shortcut to List All Column Names for a Database
    8/30/2010 2:51:25 AM by timmurphy  - Views: 532  - Comments: 1
    One of the hardest things about starting a new project is getting to know the database with which you are working. Here is a quick way to make a list of the all columns along with the name of the corr...
  • MDX Puzzle #7
    8/29/2010 9:40:12 PM by PatrickLeBlanc  - Views: 4190  - Comments: 2
    I have presented you with a couple of easy puzzles in the last few posts. So, let's step it up a little with one that is a little more challenging. I have to be honest, this puzzle was sent to me by o...
  • Restarting SQL Server using the GUI
    8/29/2010 4:23:50 PM by briankmcdonald  - Views: 4545  - Comments: 2
    Sometimes you may need to restart your instance of SQL Server. You should rarely do this on Production boxes, but in development or on your local desktop instance I restart my SQL Server instance fair...
  • Jax Code Camp - My Presentation and Projects
    8/28/2010 10:52:08 PM by briankmcdonald  - Views: 908  - Comments: 1
    First and foremost, I want to thank everyone who attended my presentation today. It got off to a late start due to the prior presentation running over and a little setup issues set me back a little bi...
  • SSRS Interactive Sorting
    8/27/2010 7:52:27 PM by SMcDonald  - Views: 574  - Comments: 0
    The other day I was creating a report and it needed some sorting capabilities. I said to myself, "Self, there may not be a lot of begginers that know how to add sorting capability". What I would like ...
  • MDX Puzzle #6 - Solution
    8/27/2010 6:15:17 PM by PatrickLeBlanc  - Views: 176  - Comments: 4
    Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX. Ok, so this is what I started with: This is a very...
  • Jax Code Camp 2010
    8/26/2010 9:28:34 PM by BradSchacht  - Views: 74  - Comments: 0
    This Saturday is Code Camp in Jacksonville, FL. There are a lot of great speakers lined up, some of which you may have seen at other events such as SQL Saturday. If your free on Saturday come on out a...
  • Adding a Bing Map Layer
    8/26/2010 2:07:20 PM by ChrisAlbrektson  - Views: 174  - Comments: 0
    With the release of the SSRS 2008 R2 reporting developers now have a new tool that they can use when creating reports. The new tool I am talking about is the new map wizard. With this you can easily s...
  • PASS Elections Portal
    8/25/2010 9:31:01 PM by sqlchicken  - Views: 87  - Comments: 0
    If you've been under a rock the last few weeks you may not know that PASS is currently undergoing their board elections process. I'm not going to rehash the entire saga here but this is just a quick p...
  • Code Camp in Auckland New Zealand -- 29th August 2010
    8/25/2010 9:11:47 PM by indupriya  - Views: 100  - Comments: 0
    The MS Communities Code Camp & Technology Summit is a Free , non-commercial event, open to everybody with an interest in any of the technologies being discussed. Development This year we will be r...
  • Changing of the Guard
    8/25/2010 8:23:15 PM by sqlchicken  - Views: 91  - Comments: 2
    A few weeks ago Andy Leonard ( Blog | Twitter ) announced that he was stepping down from his position as PASS Regional Mentor. It is with great humbleness and excitement that I can officially say I'll...