Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I get the Number of users accessing a given SQL server database from any given workstation?

Posted on 2008-10-18
14
Medium Priority
?
456 Views
Last Modified: 2008-12-06
We license our software by the number of workstations accessing our database at any given time and have a fine solution for MS Access databases. The problem is SQL server 2005. Any number of solutions I try work fine when I run them ON THE SERVER, but when I try to run them from a workstation they ALWAYS return a user count of 1. I am guessing this is because the workstations ONLY have rights to the DB in question. For simplicity We have only ONE user name and password for all users to access our DB and we want to keep it that way.

Below are some of the many methods I have tried (Please note much of this is remmed (') because it did not work and by that I mean it returned the wrong count. I was simply saving the code to remind me of what has already been tried. I am providing it only as a sample of the various things I have already tried so you do not ask me to repeat it.):

********* beginning of code/query samples *********************
 'Set RS = DB.OpenRecordset("SELECT DISTINCT (Master.dbo.sysprocesses.hostname) AS Expr1, (Master.dbo.sysdatabases.name) AS Expr2 FROM Master.dbo.sysprocesses LEFT JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid WHERE Master.dbo.sysdatabases.name IN (SELECT Table_Catalog AS Expr1 FROM INFORMATION_SCHEMA.TABLES  WHERE (table_name = 'skmaster'));", dbOpenSnapshot, dbSQLPassThrough)
Set RS = DB.OpenRecordset("SELECT  Master.dbo.sysprocesses.hostname, Master.dbo.sysdatabases.name FROM Master.dbo.sysprocesses LEFT JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid WHERE Master.dbo.sysdatabases.name = '" & SQLDBName & "';", dbOpenSnapshot, dbSQLPassThrough)
'Set RS = DB.OpenRecordset("SELECT TOP 1000 Master.dbo.sysprocesses.hostname FROM Master.dbo.sysprocesses LEFT JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid", dbOpenSnapshot, dbSQLPassThrough)
'Set RS = DB.OpenRecordset("SELECT Master.dbo.sysprocesses.hostname FROM Master.dbo.sysprocesses", dbOpenSnapshot, dbSQLPassThrough)
'Set RS = DB.OpenRecordset("use [MASTER] delete [ljskdata].dbo.sessions;insert into [ljskdata].dbo.sessions exec sp_who;", dbOpenSnapshot, dbSQLPassThrough)
'Set RS = DB.OpenRecordset("USE [ljskdata] select count(distinct hostname) AS Users from sessions where dbname= db_name();", dbOpenSnapshot, dbSQLPassThrough)
'RS.GetRows 150
 'RS.Close
'Set RS = DB.OpenRecordset("SELECT users from usercount", dbOpenSnapshot, dbSeeChanges)
'Set RS = DB.OpenRecordset("SELECT COUNT(Cnt) AS USERS FROM (SELECT DISTINCT (Master.dbo.sysprocesses.hostname) AS CNT FROM Master.dbo.sysprocesses LEFT JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid WHERE Master.dbo.sysdatabases.name = '" & SQLDBName & "') as x", dbOpenSnapshot, dbSQLPassThrough)
RS.MoveLast
'MsgBox RS!hostname
GetNoUsers = RS![Users]
GetNoUsers = RS.RecordCount

********* end of code/query samples *********************
Note that what I have tried includes running a passthrough query that stores the results in a table on the server, then retrieving the results. The return value is still always 1.

I would prefer NOT to have to give the users any more rights, but I have a hunch that may be what it takes. If so please tell me the bare minimum rights I would have to add to our user to get the desired count.

Or, perhaps, more ideally there is an EVENT which fires when a workstation attaches to a database? I could run some form of the code above on the server when that event fires that would update the user count in a table in my database which the workstation could then look at?

Thanks in advance for your help.
Kim
0
Comment
Question by:KMosher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
14 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22749571
I think you need to start by looking at

select * from sys.dm_exec_Sessions

It will give you everything from the SQL/Windows login connected to the host_name connected.  Very useful
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22760366
Brandon, there's some cool information there ... but I don't see a DB name or ID.  Do you know a table to join against to get it?  

KMosher, what I've always used for that information is
exec sp_who2
you can't put a WHERE clause on that like you could on Brandon's ... but it does give you a DBName.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 22762614
Perhaps this could add more value
select des.*,db_name(sp.dbid) from sys.dm_exec_Sessions des
join sys.sysprocesses sp
on des.session_id=sp.spid

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Author Comment

by:KMosher
ID: 22762946
Guys, thanks for all of your help but have you actually tried these solutions with a user name and password that only has rights to one DB? I have tried most of these, including sp_who and who2 and the all work fine if I log in as sa or equivalent - they return the total number of users. If I log in with as a restricted user they return one. I even created a process on the server that wrote sp_who2 to a table, executed the process with a pass through query and then queried the table - it still returned only one user when logged in as a restricted user.

Brandon, dm_exec_sessions simply maps to the sysprocesses table, which as you can see in my code above I have already tried to use. I have to think that this will give the same results.

What I need is the total number of users accessing database "A" on SQL server "B"and I need it when accessing server "B" with only rights to database "A".

If that is not possible then what I need to know is the BARE MINIMUM RIGHTS I would need to give to a user to get the information I need. The reason for this is we sell this app to many users we do not have any control over their SQL server other than access to our oun DB and our customers are understandably reluctant to grant us any more access than is absolutely neccessary to run our app.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22763259
What about creating a VIEW or Stored Procedure and granting rights on it?  You can grant a user rights to access your own view or stored procedure that, in turn, accesses something that the user would not otherwise be able to access.
0
 
LVL 2

Author Comment

by:KMosher
ID: 22763278
Again, I tried this and could not figure out how to make it work. If you can showme how..
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22763294
I'm not in front of a SQL Server ... but I think this will work ...



Create Procedure dbo.My_sp_Who2
as
Exec sp_who2
go
 
Grant Execute On My_sp_Who2 to MyLimitedUser
go

Open in new window

0
 
LVL 2

Author Comment

by:KMosher
ID: 22800685
DanielWilson,

First, sp_who2 will not give me the answer I need, I am after a user count so sp_who2 must be written to a table and then queried for distinct computers.

Second, I tried this technique and I could only get sp_who2, or the sysdatabases to return info on the the given user, it would never return info on any of the other users.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22811962
OK ... poking around in the code of sp_who2 suggests this query.

If you also needed the DB's to which they're connected, that can be brought in pretty easily as well.


Select Loginame, count(Loginame) as ConnectionCount from master.dbo.sysprocesses with (nolock)
Group By Loginame

Open in new window

0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 1000 total points
ID: 22811972
here it is w/ the database name included in the grouping:


Select D.name as Databasename, Loginame, count(Loginame) as ConnectionCount 
from master.dbo.sysprocesses P with (nolock) Inner Join 
	master.dbo.sysdatabases D on P.DBID = D.DBID
 
Group By D.name, Loginame

Open in new window

0
 
LVL 2

Author Comment

by:KMosher
ID: 22844617
Guys, I am out of town till the 13th and having trouble getting internet so I may postpone working on this for a couple of weeks.

Thanks,
Kim
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23072933
Since suggestion one's own answer can be sometimes controversial, I figured I'd chime in and say that it's a good recommendation.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question