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
453 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 250 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 250 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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