HOw do I get a user count?
-
I use an Access front end to an SQL server 2005 database. We attach with one user name and password and the users have full (owner) rights to the DB. The problem is I need a count of the number of workstations logged in to the DB. All of the techniques I have tried ALWAYS return a count of 1. I only get a correct count if I log in as administrator. I do not want to give any more rights than absolutely neccessary to my one login user. I have tried any number of techniques including pass through queries and running sp_who and storing the output in a server table and then reading the values in the table. Everything I try returns the data for only the current workstation.
If possible I would like a technique that will give me the count without adding rights to other DBs/tables. Barring that what are the minimum rights needed to get this information.
Here is a sample query that will work just fine with an sa login and return 1 with a 'MyDataBase' access only login.
SELECT COUNT (HOST) FROM (SELECT DISTINCT Master.dbo.sysprocesses.net_address AS HOST, Master.dbo.sysdatabases.name AS DB FROM Master.dbo.sysprocesses LEFT JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid WHERE Master.dbo.sysdatabases.name = 'MyDataBase') AS UC GROUP BY DB
Thanks for your help.
Kim
-
What changes in your results if you drop the 'DISTINCT' in your first line?
You stated that 'We attach with one user name and password and the users have full (owner) rights to the DB' - is everyone using the SAME user name and password?
How many connections does each individual user spawn with the database?
A yes answer to my 2nd paragraph may explain why you always see '1' as the result.
The number to my last question will help explain why the results to removing 'DISTINCT' will give you a larger number than the answer you want.
-
This is right query (except that you don't have to group it by database sinse you restrict it in your Where clause).
If you don't want to grant sa role, you can use can grant VIEW SERVER STATE permission to non-sa user.
-
Steve,
The distinct is required because my Access app actually opens multiple connections and I am only interested in returning on per machine.
Yes, SAME user name and PW. No management required.
As stated above, several connections.
The query I gave works correctly. If I have 10 users logged in using my single username and PW as each one logs on I will get 1 user as the count. If I then log in using sa or some other user with greater rights the count will be 11, with sa being the 11th user.
MARK,
It sounds like you might have the anwer I am looking for. How do I grant VIEW SERVER STATE?
And thanks both of you for your help.
-
use master
grant view server state to your_non_sa_user
go
-
Another option is to create a procedure that returns the data you need. Set the procedure to run as whatever rights it needs (such as server state) then grant execute of that to everyone. That way you fully control what data is returned back.
-
Bob,
I may be wrong, but as far as I know server permissions are granted to logins, not to procedures. In your case he will have to grant VIEW SERVER STATE to certain logins anyway.
-
Whats wrong with you that you didn't understand what I was thinking
Create a new user. Grant the appropriate privs to that user to run the needed sql.
Disable login for that user (throught windows)
USE Sales;
GO
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
SELECT user_name();
Then grant execute on the procedure to your users.
Now really, wasn't it clear the first time?
I hope this is clear now, it's 5pm Friday, and I am on vacation next week. So I am out of here.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply