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

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
LVL 2
KMosherAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
BrandonGalderisiCommented:
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
 
Daniel WilsonCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
KMosherAuthor Commented:
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
 
Daniel WilsonCommented:
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
 
KMosherAuthor Commented:
Again, I tried this and could not figure out how to make it work. If you can showme how..
0
 
Daniel WilsonCommented:
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
 
KMosherAuthor Commented:
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
 
Daniel WilsonCommented:
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
 
Daniel WilsonConnect With a Mentor Commented:
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
 
KMosherAuthor Commented:
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
 
BrandonGalderisiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.