Hi,

I posted this in the server group, but haven't gotten help, so i
thought I should try the security group.  I'm trying to give my users
the ability to run sp_who2 on SQL 2005 and
see all sessions, without granting them additional permissions.  I
thought I could do something like this:

CREATE PROCEDURE [dbo].[sp_who2_all]
WITH EXECUTE AS SELF
AS
EXEC sp_who2

The procedure creates successfully, and it does execute under a
different security context - I can see this because the login column =
sa.  However, it only returns one record, for the current session only
(as it would for a user without the VIEW SERVER STATE permission).  Of
course, if I connect as sa and run sp_who2, I see information from all
sessions.

To be clear, if I log in as sa, sp_who2 returns all sessions (as
expected).  The SP
above does in fact execute as sa (as expected).  However, it
still only lists the current session, as if it's executing without the
VIEW SERVER STATE perm.  That's the piece I don't understand, and what
I need a solution for.

Why is it behaving this way, and is there a good workaround?  Is this
only my environment?  If someone could confirm the behaviour, that
might help.

Thanks -  stavros