Hi,

In SQL 2000, users could select from master.dbo.sysprocesses to determe
the total number of users currently in that database
e.g.
select DISTINCT Loginame, Program_Name, Hostname
from master.dbo.sysprocesses
where master.dbo.sysprocesses.dbid = db_id()
and master.dbo.sysprocesses.Program_Name = 'XYZ'

In SQL 2005 sysprocesses (and the new sys.dm_exec_sessions) will only
return the current users processes, unless the user has the
processadmin server role.

In order to allow normal users to get the current users logged in, we
made a stored procedure like
CREATE PROCEDURE [dbo].[spUsersLoggedIn] WITH EXECUTE AS 'dbo' AS
set nocount on
-- select user_name()
select distinct host_name, program_name, login_name
from sys.dm_exec_sessions
where is_user_Process = 1 and program_name like 'XYZ'
return 0
go

with execute rights assigned to the relevant database role.
When this is run, Profiler shows the 'select *..' line does run with
the 'sa' loginname, however only 'sa' processes are returned.

Is there any way for normal SQL 2005 users to get a count of users in
an application (Except for adding them to a role with the processadmin
server role)

Thanks
Adam

(strideryb@hotmail.com) writes:
[quoted text, click to view]

More precisely, they need the permission VIEW SERVER STATE.

[quoted text, click to view]

Unless the database is set as trustworthy, I would not expect this procedure
to return anything at all, as the EXECUTE AS causes you to be sandboxed
into the database.

[quoted text, click to view]

Yes, rather than granting them permissions, you can sign the procedure
with a certificate, associate that certificate with a login and then
grant that login VIEW SERVER STATE. I have an article on my web site that
discusses this. See http://www.sommarskog.se/grantperm.html#Certificates.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at