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
449 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now