EXECUTE AS in SQL 2005 - does it always work?

Hi,
Looks like "EXECUTE AS" is not always works as expected after reading help.
Lets try next steps for demonstration:
1. Create SQL login ( lets say "MASTER" ) with "procesmin" Server Role an
d
use it to connect to any database (with 'dbo').
2. Execute "SELECT * from master.dbo.sysprocesses" statement and mark the
result (all records).
3. Crete next SP and execute it
CREATE PROCEDURE Master.MxSys_SysProcesses
WITH EXECUTE AS CALLER
--WITH EXECUTE AS OWNER
--WITH EXECUTE AS 'MASTER'
AS
BEGIN
SELECT * from master.dbo.sysprocesses
RETURN 0
END
Mark results (also all records).
4. Drop the SP and recreate it with "WITH EXECUTE AS OWNER" or "WITH
EXECUTE AS 'MASTER'" option.
Execute it. See results. They are different from #2 and #3 (just one
record!). But expected to be the same.
When I saw it I said: "Oops!". What would you say? Bug or somethign missing
in help?
BHW, the same is true for sys.dm_exec_requests and sys.dm_exec_sessions view
s.
Thanks.You may want to post this in the SQL Server 205 Beta/CTP newsgroups
http://communities.microsoft.com/ne...lcid=us

What version of the CTP?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dweller" <Dweller@.discussions.microsoft.com> wrote in message
news:A8DC5918-7D26-425C-851B-F5EE9BF6A4F3@.microsoft.com...
> Hi,
> Looks like "EXECUTE AS" is not always works as expected after reading
> help.
> Lets try next steps for demonstration:
> 1. Create SQL login ( lets say "MASTER" ) with "procesmin" Server Role
> and
> use it to connect to any database (with 'dbo').
> 2. Execute "SELECT * from master.dbo.sysprocesses" statement and mark the
> result (all records).
> 3. Crete next SP and execute it
> CREATE PROCEDURE Master.MxSys_SysProcesses
> WITH EXECUTE AS CALLER
> --WITH EXECUTE AS OWNER
> --WITH EXECUTE AS 'MASTER'
> AS
> BEGIN
> SELECT * from master.dbo.sysprocesses
> RETURN 0
> END
> Mark results (also all records).
> 4. Drop the SP and recreate it with "WITH EXECUTE AS OWNER" or "WITH
> EXECUTE AS 'MASTER'" option.
> Execute it. See results. They are different from #2 and #3 (just one
> record!). But expected to be the same.
> When I saw it I said: "Oops!". What would you say? Bug or somethign
> missing
> in help?
> BHW, the same is true for sys.dm_exec_requests and sys.dm_exec_sessions
> views.
> Thanks.|||
"Mike Epprecht (SQL MVP)" wrote:

> You may want to post this in the SQL Server 205 Beta/CTP newsgroups
> http://communities.microsoft.com/ne...er2005&slcid=us

Thanks, I will.

> What version of the CTP?
September.

> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dweller" <Dweller@.discussions.microsoft.com> wrote in message
> news:A8DC5918-7D26-425C-851B-F5EE9BF6A4F3@.microsoft.com...
>
>