EXECUTE AS question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stavros - 07 Mar 2008 23:28 GMT

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

Reply to this Message

DDD - 08 Mar 2008 13:54 GMT

> 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
[quoted text clipped - 15 lines]
>
> 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.

Reply to this Message

stavros - 10 Mar 2008 17:21 GMT

> > 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
[quoted text clipped - 21 lines]
> 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.

Reply to this Message

stavros - 11 Mar 2008 22:04 GMT

> 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
[quoted text clipped - 15 lines]
>
> 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

Reply to this Message