January 1, 2011 at 5:43 am
I've seen this topic addressed in numerous posts all over the web, but have not run across any reasonable, sensible solution. It doesn't seem to me that it should be such a problem.
I want non-admin users to be able to see who else is logged into a database. I have a small routine that queries the following view a few times per minute:
SELECT LTRIM(RTRIM(hostname)) AS Uzivatel
FROM sys.sysprocesses
WHERE (hostname <> '')
GROUP BY hostnameThere is only one department using this DB and there are no security concerns or sensitive data that dictate exclusion of prying eyes, only the standard concerns of accidental gefingerpoken und mittengrabben. What I want to handle is the possibility of collisions (remote, but not impossible) so that a user can see who else might be accessing the same data.
I have tried setting admin permissions on the view, calling it from a stored procedure and setting admin execute permissions on the procedure, setting admin select permissions on sys.sysprocesses, all without effect. The view executes, but shows only the user's own name, unless the user is logged in as an administrator. For obvious reasons, I don't want to make all users administrators, I only want to show them this one piece of information. Is there a way?
January 1, 2011 at 6:16 am
I beleive you need to explicitly grant View Server State permissions to each of the users.
"GRANT VIEW SERVER STATE TO user"
January 1, 2011 at 6:39 am
Nawar.Tabaa (1/1/2011)
I beleive you need to explicitly grant View Server State permissions to each of the users."GRANT VIEW SERVER STATE TO user"
I'll be damned - that did it. I tried that yesterday, but now I think I recall that I used
GRANT VIEW SERVER STATE TO PaleoUzivatel
which executed without complaint, but didn't help. This time, I used
GRANT VIEW SERVER STATE TO [PALEO-SERVER\PaleoUzivatel]
and it's working. Thank you.
January 1, 2011 at 10:28 am
Update - it quits working when I log out of SSMSE and restart the user app. When I log into SSMSE as sa, it starts working again - I don't have to execute the GRANT statement again, just log in as sa. What do I need to do to make the setting permanent?
January 2, 2011 at 2:19 am
Just make sure that the SSMS's connected session is by the user granted the "VIEW SERVER STATE".
January 2, 2011 at 1:43 pm
Nawar.Tabaa (1/2/2011)
Just make sure that the SSMS's connected session is by the user granted the "VIEW SERVER STATE".
Sorry, I don't understand that. Does that mean I should log in as the non-admin user to execute the VIEW SERVER STATE command? Surely that can't be right - a non-admin user can't give himself permissions in a database. I must be logged in as a user with administrative privilege to perform such tasks, no?
Maybe I didn't make myself clear. When I logged in as sa and executed the VIEW SERVER STATE command, the app logged in with user permission was able to see the info from the sysprocesses table. When I close SSMSE, that ability disappears and users once again see only their own info. When I start SSMSE again and login as sa again, the user regains the ability to see other process info, even though I DO NOT execute the VIEW SERVER STATE command again. Simply logging in as sa and doing nothing else allows users to suddenly see the info again.
If I was clear the first time and your advice is based on a correct understanding of my problem, then would you please explain it a little? I don't understand what you are telling me to do.
January 2, 2011 at 11:54 pm
The user granted "VIEW SERVER STATE", must always be able to query sysprocesses for ALL users.
Please double check that it's already granted it, and you are not mixing between users sessions while using the SSMS.
January 3, 2011 at 4:27 am
Nawar.Tabaa (1/2/2011)
The user granted "VIEW SERVER STATE", must always be able to query sysprocesses for ALL users.Please double check that it's already granted it, and you are not mixing between users sessions while using the SSMS.
Didn't I do that by executing
GRANT VIEW SERVER STATE TO [PALEO-SERVER\PaleoUzivatel] ?
PaleoUzivatel is the name of a group in the OS, to which all my non-admin users belong. I understood your first response to mean that GRANT VIEW SERVER STATE is what gives such a login permission to query sysprocesses for all users. If I misunderstood, how then do I ensure that anyone in the PaleoUzivatel group can execute this view, regardless of whether I am at that moment logged in to SSMSE as sa?
January 3, 2011 at 6:20 am
What is the version you are using?
January 3, 2011 at 6:51 am
Nawar.Tabaa (1/3/2011)
What is the version you are using?
MsAccess 2007 runs the application, client machines have XP, Vista and 7.
Windows Server 2008 is the server OS.
SQL Server 2008 Express R2 is the DB engine.
Here is the info copied from SSMSE on my 32-bit XP development machine:
Microsoft SQL Server Management Studio10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer8.0.6001.18702
Microsoft .NET Framework2.0.50727.3615
Operating System5.1.2600
Here is the info copied from SSMSE on the 64-bit Server
Microsoft SQL Server Management Studio10.50.1600.1
Microsoft Data Access Components (MDAC)6.0.6002.18005
Microsoft MSXML3.0 5.0 6.0
Microsoft Internet Explorer7.0.6002.18005
Microsoft .NET Framework2.0.50727.4206
Operating System6.0.6002:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply