EXECUTE AS question

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.
Why is it behaving this way, and is there a good workaround?
Thanks - stavrosOn Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
> 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.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
sp_who requires VIEW SERVER STATE permission on the server to see all
executing sessions on the instance of SQL Server. Otherwise, the user
sees only the current session.
Please refer http://msdn2.microsoft.com/en-us/library/ms174313.aspx
and http://msdn2.microsoft.com/en-us/library/ms186717.aspx.|||On Mar 8, 6:54 am, DDD <1983...@.gmail.com> wrote:
> On Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
>
> > 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.
> > Why is it behaving this way, and is there a good workaround?
> > Thanks - stavros
> sp_who requires VIEW SERVER STATE permission on the server to see all
> executing sessions on the instance of SQL Server. Otherwise, the user
> sees only the current session.
> Please referhttp://msdn2.microsoft.com/en-us/library/ms174313.aspx
> andhttp://msdn2.microsoft.com/en-us/library/ms186717.aspx.
Agreed - sorry if I wasn't clear, but that's the whole point. My
users don't have VIEW SERVER STATE permission, so I'm trying to write
a SP which will EXECUTE AS a user who does have that permission (sa).
If I log in as sa, sp_who2 returns all sessions (as expected). The SP
I described 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.|||On Mar 7, 4:28 pm, stavros <stav...@.mailinator.com> wrote:
> 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.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
Can someone try this same exercise and see if they get the same
results? I still can't figure out this behavior, so maybe it's
something particular to my environment. Thanks - Stavros